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:jason@billtron.com] > Sendt: 3. juli 2003 22:39 > Til: php-db@lists.php.net > Emne: 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 jason@billtron.com. > > 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
