Jeremy Cole wrote:
Hi,
Thanks, but unless I'm missing something, that will return the same as SUBJECT="English" and (GRADE="1" or GRADE="2"), which is resource records 1,2,3, and 4 - too many matches. Am I missing something?
How about this:
SELECT resources.id, resources.title FROM resources LEFT JOIN goals AS goal_a ON resources.id=goal_a.resource_id AND goal_a.subject="English" AND goal_a.grade=1 LEFT JOIN goals AS goal_b ON resources.id=goal_b.resource_id AND goal_b.subject="English" AND goal_b.grade=2 WHERE goal_a.id IS NOT NULL AND goal_b.id IS NOT NULL
<snip>
There's no need for a LEFT JOIN here. The difference between LEFT JOIN and JOIN is that LEFT JOIN creates extra NULL rows for the table on the right whenever it doesn't have a match for the table on the left. We don't need that here. Indeed, you throw those NULL rows away with your WHERE clause. It's more efficient not to create them in the first place if they're not needed. In general, any time you write
...LEFT JOIN table_on_right ... WHERE table_on_right.some_col IS NOT NULL
you should probably just use a JOIN instead.
Hence, this query would be better as
SELECT resources.id, resources.title
FROM resources
JOIN goals AS goal_a
ON resources.id = goal_a.resource_id
AND goal_a.subject = "English" AND goal_a.grade = 1
JOIN goals AS goal_b
ON resources.id = goal_b.resource_id
AND goal_b.subject = "English" AND goal_b.grade = 2Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
