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