Have a look at the manual page for EXISTS, you appear to need something like
SELECT * FROM resources AS r
WHERE EXISTS (
SELECT resource_id FROM goals AS g
WHERE g.resource_id = r.id AND grade=1 AND subject='English'
)
AND EXISTS (
SELECT resource_id FROM goals AS g
WHERE g.resource_id = r.id AND grade=2 AND subject<>'English'
)
PB
-----
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. My MySQL is
> version 4.0.23a, if that makes a difference.
>
> 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
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.7 - Release Date: 2/10/2005
