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.

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

You want a join, and it is better imho to make it explicit so you can
see more clearly what you're doing (that's the way I do it, anyway).
The join takes care of lining up the resources table and the goals
table on the resourcesID.

[By the way, I find it easier to name the tables in the singular, since
when you write code for them, you will be referring to them (and
thinking of them) in the singular. You will have a "goal" and a
"resource". Try it and you'll see.]

Try:

SELECT DISTINCT title FROM resources
LEFT JOIN goals on goals.resourcesID = resources.ID
WHERE grade IN (1,2) AND subject LIKE 'english'

You'll need the DISTINCT so you don't get repeated titles.

anmar.TakeThisOut@intrex.net 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.
 >
 > 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<!-- ~MESSAGE_AFTER~ -->