AM Thomas wrote:
Guarded exclamations of success!
This seems to be working for me so far - thank you! Here's an actual example of a working query on my database; the field names are a little different (sorry, I was trying to make my earlier example more readable). The main change, though, is that I did plain 'join' instead of 'left join', which seems to make it much faster. It was pretty slow at first.
Yes, LEFT JOIN does extra work, and it wasn't needed here.
mysql> select r.id, ga.Grade, ga.GoalNumber, gb.GoalNumber from resources as r
join resource_goals as ga on r.id=ga.ResourceID
and ga.Grade='4th' and ga.GoalNumber='1'
join resource_goals as gb on r.id=gb.ResourceID
and gb.Grade='4th' and gb.GoalNumber='2'
where ga.goal_id IS NOT NULL
and gb.goal_id IS NOT NULL
group by r.id;
There are some strange things here, I think.
* You've never shown us your table definitions, but I would have expected GoalNumber to be an integer, not a string. If so, you shouldn't quote the numbers you compare it to.
* I think it unlikely that you have rows with values in Grade, GoalNumber, and ResourceID which have NULL for goal_id. Isn't goal_id the primary key? If I'm right, you don't need your WHERE clause.
* Why have you added "GROUP BY r.id"? Was that an attempt to fix something? If we've got the query right, there should be no need for grouping. If you don't get the result you want without the GROUP BY, then you should let us know, because that would mean we've missed something.
Putting those together, I'd expect
SELECT r.id, ga.Grade, ga.GoalNumber, gb.GoalNumber
FROM resources AS r
JOIN resource_goals AS ga
ON r.id = ga.ResourceID
AND ga.Grade = '4th'
AND ga.GoalNumber = 1
JOIN resource_goals AS gb
ON r.id = gb.ResourceID
AND gb.Grade = '4th'
AND gb.GoalNumber = 2to do the job. Does it?
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
