Re: select where multiple joined records match

Before I answer this, it would help if you clarified what kind of 
results you want.  Please illustrate in a table what kind of output 
you want. -- Darren Duncan

At 8:13 AM -0500 2/13/05, 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.  And, yes, I'm
>doing it with a Perl script.  My MySQL is version 4.0.23a, if that makes a
>difference.
>
>Please correct me if this should go to a more general list.
>
>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.

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]