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