MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:AM Thomas Date:February 13 2005 1:22pm
Subject:select where multiple joined records match
View as plain text  
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
-- 
Virtue of the Small / (919) 929-8687
Thread
select where multiple joined records matchAM Thomas13 Feb
  • Re: select where multiple joined records matchPeter Brawley13 Feb
    • Re: select where multiple joined records matchMichael Stassen13 Feb
  • Re: select where multiple joined records matchMichael Stassen13 Feb
    • more complexity (was: select where multiple joined records match)AM Thomas14 Feb
  • RE: select where multiple joined records matchGordon14 Feb
    • Re: select where multiple joined records matchAM Thomas14 Feb
      • Re: select where multiple joined records matchJeremy Cole14 Feb
        • Re: select where multiple joined records matchAM Thomas15 Feb
          • Re: select where multiple joined records matchMichael Stassen16 Feb
            • Re: select where multiple joined records matchAM Thomas16 Feb
              • Re: select where multiple joined records matchMathew Ray17 Feb
              • Re: select where multiple joined records matchMichael Stassen21 Feb
                • Re: select where multiple joined records matchAM Thomas21 Feb
        • Re: select where multiple joined records matchMichael Stassen16 Feb
Re: more complexity (was: select where multiple joined records match)Harald Fuchs14 Feb