MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:February 16 2005 3:52pm
Subject:Re: select where multiple joined records match
View as plain text  
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 = 2

Michael
Thread
select where multiple joined records matchAM Thomas13 Feb
  • Re: select where multiple joined records matchPeter Brawley13 Feb
    • Re: select where multiple joined records matchMichael Stassen13 Feb
  • Re: select where multiple joined records matchMichael Stassen13 Feb
    • more complexity (was: select where multiple joined records match)AM Thomas14 Feb
  • RE: select where multiple joined records matchGordon14 Feb
    • Re: select where multiple joined records matchAM Thomas14 Feb
      • Re: select where multiple joined records matchJeremy Cole14 Feb
        • Re: select where multiple joined records matchAM Thomas15 Feb
          • Re: select where multiple joined records matchMichael Stassen16 Feb
            • Re: select where multiple joined records matchAM Thomas16 Feb
              • Re: select where multiple joined records matchMathew Ray17 Feb
              • Re: select where multiple joined records matchMichael Stassen21 Feb
                • Re: select where multiple joined records matchAM Thomas21 Feb
        • Re: select where multiple joined records matchMichael Stassen16 Feb
Re: more complexity (was: select where multiple joined records match)Harald Fuchs14 Feb