List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 13 2005 5:24pm
Subject:Re: select where multiple joined records match
View as plain text  
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

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