Hi,
You could try something like:
SELECT school_name, sID
FROM schools
WHERE sID in (select sid from school_highlight_details
where hid = 2)
and sID in (select sid from school_highlight_details
where hid = 4)
GROUP BY sID
If you are using mysql this probably won't work. I think mysql doesn't like subselects.
hth Henrik Hornemann
> -----Oprindelig meddelelse-----
> Fra: Jason Givhan [mailto:[EMAIL PROTECTED]
> Sendt: 3. juli 2003 22:39
> Til: [EMAIL PROTECTED]
> Emne: [PHP-DB] Re: A complex query problem
>
>
> I’m sorry, let me clarify the problem: I have some schools
> with multiple
> highlights. I want to search for a school that has BOTH highlights
> ‘Sports’ AND ‘Graduation’.
>
> I can search for a school that has EITHER ‘Sports’ OR
> ‘Graduation’ using
> this query:
>
> SELECT schools.school_name, schools.sID
> FROM schools, school_highlight_details
> WHERE schools.sID = school_highlight_details.sid
> AND (school_highlight_details.hid = 2
> OR school_highlight_details.hid = 4 )
> GROUP BY schools.sID
>
> But now I want to search for a school that has BOTH
> highlights ‘Sports’
> AND ‘Graduation’.
>
> I have 3 tables:
>
> 1. school (sID, school_name)
> 2. highlights (hID,highlight)
> 3. school_highlight_details (dID,sid,hid).
>
>
>
> Jason Givhan wrote:
> > I have 3 tables:
> >
> > 1. school (sID, school_name)
> > 2. highlights (hID,highlight)
> > 3. school_highlight_details (dID,sid,hid).
> >
> > I have set up a search to allow the user to submit a school name OR
> > highlights(highlights is set up in an array of checkboxes),
> and then
> > displaying any school name that fullfill the requirements.
> >
> > What I need to do now is set up an array to search for a
> school name AND
> > highlights. If a person types in a name (or just one
> letter from a
> > name, like 'm') AND they check off a couple of highlights
> checkboxes(ex:
> > Sports and Graduation), a query should return the results
> of any school
> > name starting with 'm' that has the highlights of 'Sports' and
> > 'Graduation'.
> >
> > If you think you can help me, just email me at [EMAIL PROTECTED]
> > If you need more information, let me know.
> >
> > Thank you,
> >
> > Jason Givhan
> >
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
