MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:AM Thomas Date:February 14 2005 6:51pm
Subject:Re: select where multiple joined records match
View as plain text  
Thanks, but unless I'm missing something, that will return the same as  
SUBJECT="English" and (GRADE="1" or GRADE="2"), which is resource records  
1,2,3, and 4 - too many matches.  Am I missing something?

- AM



On Mon, 14 Feb 2005 12:30:44 -0600, Gordon <gordon@stripped> wrote:

> Try this
>
> Select *
> from   resources, goals
> where  resources.ID = goals.RESOURCE_ID
>        and (SUBJECT="English"
>             and GRADE="1")
>             OR
>            (SUBJECT="English"
>             and GRADE="2");
>
> -----Original Message-----
> From: AM Thomas [mailto:am@stripped]
> Sent: Sunday, February 13, 2005 7:23 AM
> To: mysql@stripped
> Subject: select where multiple joined records match
>
> 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