refer to 1.7.4.1 Subselects in the manual for the answer. here's a brief example:
The queries: SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2); SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2 WHERE table1.id=table2.id); Can be rewritten as: SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; --- Nicholas Stuart <[EMAIL PROTECTED]> wrote: > Ok I'm stumped on what I think should be a somewhat > simple query. What I > have so far is a list of names that is in a list of > projects AND in a the > main contact list by doing the following query: > SELECT p.name, p.company FROM contacts c, projects p > WHERE > CONCAT(c.firstName, " ", c.lastName) = p.name AND > c.company = p.company > > This is good and works correctly, what I need now is > the opposite of this. > The names that are in the project list but NOT in > the contact list. If I > had some subqueries this would be a simple NOT IN :) > but as I dont (mysql > 3.23.55) I'm not sure how to attack this. > > Thanks for any pointers/advice. > -Nick > > > > --------------------------------------------------------------------- > 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 > __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com --------------------------------------------------------------------- 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
