MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Jeremy Cole Date:February 14 2005 7:07pm
Subject:Re: select where multiple joined records match
View as plain text  
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

-- 
Jeremy Cole
Technical Yahoo - MySQL (Database) Geek
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