MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:AM Thomas Date:February 15 2005 1:53am
Subject:Re: select where multiple joined records match
View as plain text  
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.

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;


- AM




On Mon, 14 Feb 2005 11:07:48 -0800, Jeremy Cole <jcole@stripped>  
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
>
> Alternately:
>
> SELECT resources.id, resources.title, COUNT(*) as nr
> FROM resources
> LEFT JOIN goals
>    ON resources.id=goals.resource_id
>    AND goals.subject="English"
> WHERE goals.grade IN (1, 2)
> GROUP BY resources.id
> HAVING nr = 2
>
> (The above is untested, since you didn't provide your example table in  
> SQL, and I am unwilling to re-format it into CREATE TABLE/INSERT  
> statemnts to test things.  The concepts are solid.)
>
> Regards,
>
> Jeremy
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