I wrote: >I apologize for asking such a basic SQL question, but I am failing in finding >the solution... >Consider the following table: >id | option >----------- >1 | a >2 | a >2 | b >I want to find the ids with only a specific set of options. >For example, if I wanted to get the ids which have option a only, the query >should give me 1 as a result, not 1 and 2. >I suspect I need to use a LEFT JOIN on the same table, but I have failed >miserably so far... >Many thanks in advance to the SQL experts ;-).
Thank you very much to all for your responses: it led me to the solution and in the process improved my SQL level: select id from t left join t as u on (t.id = u.id) and (t.option = u.option) and ( (t.option = 'a') or (t.option = 'b') or ... ) group by t.id having (count(*) = count(u.id)) and (count(*) = N) * with N = number of options to match Thanks again! Jean-Luc (http://jfontain.free.fr/moodss/) --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php