AM Thomas wrote:
I'm trying to figure out how to select all the records in one table
which have multiple specified records in a second table. My MySQL is version 4.0.23a, if that makes a difference.
Here's a simplified version of my problem.
I have two tables, resources and goals.
resources table:
ID TITLE 1 civil war women 2 bunnies on the plain 3 North Carolina and WWII 4 geodesic domes
goals table:
ID RESOURCE_ID GRADE SUBJECT 1 1 1 English 2 1 1 Soc 3 1 2 English 4 2 1 English 5 2 3 Soc 6 3 2 English 7 4 1 English
Now, how do I select all the resources which have 1st and 2nd grade English goals? If I just do:
Select * from resources, goals where ((resources.ID = goals.RESOURCE_ID) and (SUBJECT="English") and ((GRADE="1") and (GRADE="2")));
I'll get no results, since no record of the joined set will have more than one grade. I can't just put 'or' between the Grade conditions; that would give resources 1, 2, 3, and 4, when only 1 really should match.
SELECT r.TITLE FROM resources r JOIN goals g ON (r.ID=g.RESOURCE_ID) WHERE g.SUBJECT = 'English' AND (g.GRADE = 1 OR g.GRADE = 2) GROUP BY r.TITLE HAVING COUNT(*) = 2;
This can be generalized. Put the OR-separated list of grades to be matched in the WHERE clause, and change the row count in the HAVING clause to be the number of grades required.
My real problem is slightly more complex, as the 'goals' table also contains an additional field which might be searched on.
No problem.
SELECT r.TITLE
FROM resources r JOIN goals g ON (r.ID=g.RESOURCE_ID)
WHERE g.SUBJECT = 'English'
AND g.additional_field = 'whatever'
AND (g.GRADE = 1 OR g.GRADE = 2)
GROUP BY r.TITLE
HAVING COUNT(*) = 2;I'm thinking it's time for me to go into the deep end of SQL (MySQL, actually), and my old O'Reilly MySQL & mSQL book isn't doing the trick.
Surely this has come up before - thanks for any guidance.
- AM Thomas
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
