Re: select where multiple joined records match

[Jeremy Cole]

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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]