Thank you kindly for your gracious help.
I am making much progress (the 'as r' and 'as g' syntax is helpful; I'd forgotten about it since I'm doing most of this through Perl; thanks). I played with the COUNT solution for a while and was initially encouraged, nay, ecstatic.
However, I was getting weird results and realized that I had left out a wrinkle in my example (and my thinking): the extra field in my goals table means that the other values can, as a group, be repeated for several rows. This seems to keep this solution from working. Mea culpa; my example wasn't adequate.
There might be multiple records with a given subject and grade combination, due to the additional field (called "GoalNumber") in the goals table. The combination of ResourceID, Subject, Grade, and GoalNumber will be unique, but the combination of ResourceID, Subject, and Grade need not be.
Here's a revision of my example.
resources table:
ID TITLE 1 Got Your Nose 2 Goats and Waterfowl: A Promising Alliance 3 North Carolina and WWIIb 4 Geodesic Domes - Ivy Revolution
goals table:
(I know all the numbers make it hard to read, sorry; I'll try to improve readability by putting a blank line before a new RESOURCE_ID).
ID RESOURCE_ID GRADE GOALNUMBER SUBJECT 1 1 1 1 English 2 1 1 2 English 3 1 1 3 English 4 1 1 1 Soc 5 1 2 5 English 6 1 2 6 English 7 1 2 1 English 8 1 2 2 English
9 2 1 1 English 10 2 3 1 Soc
11 3 2 1 English 12 3 2 7 English 13 3 2 9 English
14 4 1 1 English
Now, if I understand how this is working:
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.ID
HAVING COUNT(*) = 2;will give an incorrect result, because the number of rows returned for each matching ID will be unpredictable. It could be 7 rows for ID = 1 (which is a correct match), or 3 rows for ID = 3 (which shouldn't match since it only has grade 2).
I wish the EXISTS solution offered by Mr. Brawley would work, but thanks to Mr. Stassen for the clue about MySQL version. I tried it anyway at the command line but, of course, it didn't work.
I also found a reference to an INTERSECTION keyword and experimented with it briefly; I couldn't find a document that listed when certain features came into MySQL, so I don't know if INTERSECTION is completely out of the picture, though UNION seems to have arrived after 4.0. Is there such a document? Or do I have to look at the change log for each version?
( Running "mysql -V" actually gives me: Ver 12.22 Distrib 4.0.23a )
- AM Thomas
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
