* scott > Can you explain the query if you have time! [...] > select t1.* > from test t1 > left join test t2 on > t2.id=2 and > t2.item=t1.item > where t2.id is NULL;
This is a self join. Read one table, left join to the same table looking for the records you do _not_ want, and then in the WHERE clause check for NULL in the left-joined table. Note that it is necessary to use aliases for the table name in this case. Also, note that the match criteria for the join is placed in the ON clause, not in the WHERE clause, while the match criteria for the final result ("t2.id is NULL", wich means we did not find a t2 row) is in the WHERE clause. <URL: http://www.mysql.com/doc/en/SELECT.html > <URL: http://www.mysql.com/doc/en/JOIN.html > -- 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