A Left join using IS NULL will work. You can get the syntax and see an
example in the manual
http://www.mysql.com/doc/en/JOIN.html
>From the maunal...
If there is no matching record for the right table in the ON or USING part
in a LEFT JOIN, a row with all columns set to NULL is used for the right
table. You can use this fact to find records in a table that have no
counterpart in another table:
mysql> SELECT table1.* FROM table1
-> LEFT JOIN table2 ON table1.id=table2.id
-> WHERE table2.id IS NULL;
This example finds all rows in table1 with an id value that is not present
in table2 (that is, all rows in table1 with no corresponding row in table2).
This assumes that table2.id is declared NOT NULL, of course. See section
5.2.6 How MySQL Optimises LEFT JOIN and RIGHT JOIN.
-----Original Message-----
From: Nicholas Stuart [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 04, 2003 9:25 AM
To: [EMAIL PROTECTED]
Subject: Opposite selection...
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
