Re: select where multiple joined records match

You need to first do a self join on the table to get
one table identifying RESOURCE_IDs that meet your
criteria:


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


create temporary table tmp as
select G1.RESOURCE_ID
from goals G1 inner join goals G2 on
     G1.RESOURCE_ID = G2.RESOURCE_ID
 and G1.SUBJECT     = G2.SUBJECT 
 and 
where
      GR1.GRADE = 1 
  and GR2.GRADE = 2;

THEN:

select * 
from tmp inner join goals g on 
     tmp.resource_id = g.resource_id
where g.GRADE in (1, 2);




-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]