select where multiple joined records match

I'm trying to figure out how to select all the records in one table
which have multiple specified records in a second table. And, yes, I'm
doing it with a Perl script. My MySQL is version 4.0.23a, if that makes a
difference.

Please correct me if this should go to a more general list.

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.

My real problem is slightly more complex, as the 'goals' table also
contains an additional field which might be searched on.

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
--
Virtue of the Small / (919) 929-8687

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: select where multiple joined records match

Before I answer this, it would help if you clarified what kind of
results you want. Please illustrate in a table what kind of output
you want. -- Darren Duncan

At 8:13 AM -0500 2/13/05, 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. And, yes, I'm
>doing it with a Perl script. My MySQL is version 4.0.23a, if that makes a
>difference.
>
>Please correct me if this should go to a more general list.
>
>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.
>
>My real problem is slightly more complex, as the 'goals' table also
>contains an additional field which might be searched on.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

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/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org