Hi Andy What you can do is make a "copy" of your genre_titles table through aliasing, this will allow you to only return the results that have both genres and should reduce the need for programmatical sorting - aliasing also makes for less typing :).
SELECT a.name, b.titleid FROM titles a, genre_titles b, genre_titles c WHERE b.genid = 4 AND (b.titleid = c.titleid AND c.genid = 5) AND a.titleid = b.titleid If the number of genres that have to be matched vary, you can always generate your code through a script that loops through and builds the additional parts of the predicate. HTH Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] "There are 10 kinds of people in this world, those who understand binary and those who don't" (Unknown) ----- Original Message ----- From: "Andrew Barnes" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, October 14, 2003 2:46 AM Subject: SELECT problem > Hi, > > I have three tables, a title's table, a genre's table and a genre_titles map > table (to model the many to many relationship between genre's and title's). > > I need to write a query that will return title's that match two or more > genre's. An example would be one title could be a comedy/drama and I would > need to find other title's that have a reference to the genre's comedy and > drama. I have tried this query - > > "select titles.name, genre_titles.titleid from titles, genre_titles > where genre_titles.genid = 4 and genre_titles.titleid = titles.titleid > or genre_titles.genid = 5 and genre_titles.titleid = titles.titleid" > > with programmatic sorting but the result sets are too large and the sorting > algorithm is too slow. I was wondering whether there was a query that would > return the exact result set needed. I am using mysql 4.0.13 > > > Regards > AndyB > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
