MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:February 13 2005 8:37pm
Subject:Re: select where multiple joined records match
View as plain text  
Except that he/she is using 4.0, which doesn't support subqueries.

Michael

Peter Brawley wrote:

> 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
> 
> 
> 
> 
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