MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:February 13 2005 8:19pm
Subject:Re: select where multiple joined records match
View as plain text  
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.

   SELECT r.TITLE
   FROM resources r JOIN goals g ON (r.ID=g.RESOURCE_ID)
   WHERE g.SUBJECT = 'English'
     AND (g.GRADE = 1 OR g.GRADE = 2)
   GROUP BY r.TITLE
   HAVING COUNT(*) = 2;

This can be generalized.  Put the OR-separated list of grades to be matched 
in the WHERE clause, and change the row count in the HAVING clause to be the 
number of grades required.

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

No problem.

   SELECT r.TITLE
   FROM resources r JOIN goals g ON (r.ID=g.RESOURCE_ID)
   WHERE g.SUBJECT = 'English'
     AND g.additional_field = 'whatever'
     AND (g.GRADE = 1 OR g.GRADE = 2)
   GROUP BY r.TITLE
   HAVING COUNT(*) = 2;

> 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

Michael
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