* Jerry Rehak > I have a table with the columns names and id. I want to be able > to find all > names with an id of '03' that do not have other records with id values of > '10','20' or '37'. > > Is this even possible to do? > > name id > a 03 > a 11 > a 12 > a 13 I want 'a' because it has a 03 and not a 10, a 20, OR 37 > b 03 > b 10 I don't want 'b' because it has a 10 > c 04 > c 11 > c 20 I don't want 'c' because it doesn't have a 03 > d 03 I want 'd' because it has a 03 and no other records
This can be done with a simple LEFT JOIN: SELECT t1.* FROM table AS t1 LEFT JOIN table AS t2 ON t2.name=t1.name AND t2.id IN ('10','20','37') WHERE t1.id='03' AND t2.id IS NULL; We select the rows we want from t1, left join with the rows we don't want (t2), and put as a condition in the where clause that we only want rows where t2 was not found. -- Roger --------------------------------------------------------------------- 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