RE: Opposite selection...
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
Re: Opposite selection...
I think you are coming from the wrong angle. If you want the opposite, you should reverse your approach. Select all the names in the projects table, match them with names in the contacts, then filter it so you only have the ones without a match. SELECT p.name,p.company FROM projects p LEFT JOIN contacts c ON p.name=CONCAT(c.firstName, " ", c.lastName) WHERE c.firstname IS NULL I used the firstname field simply because I don't know your other column names. I don't know you data setup, so you may need to add a distinct clause in there to avoid duplicate names. On Tuesday, February 4, 2003, at 12:25 PM, Nicholas Stuart 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. -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - 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
Re: Opposite selection...
Hi. On Tue 2003-02-04 at 12:25:08 -0500, [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. Well, manual explicitly explains how to cope with the lack of sub-selects: http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html So something like SELECT p.name, p.company FROM project p LEFT JOIN contacts c ON c.company = p.company AND CONCAT(c.firstName, " ", c.lastName) = p.name WHERE c.lastName IS NULL should do the job. This won't be able to use indexes due to the expression (CONCAT) over the right-table columns (this was not true for your original example, because a normal joins allows exchanges the order, a LEFT JOIN doesn't - a sub-select wouldn't help with this, btw). If you are sure that no spaces are in firstname resp. lastname, you can rewrite the condition to enable use of indexes: ON c.company = p.company AND c.firstName = SUBSTRING_INDEX( p.name, ' ', 1 ) AND c.lastName = SUBSTRING_INDEX( p.name, ' ', -1 ) HTH, Benjamin. -- [EMAIL PROTECTED] - 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
Re: Opposite selection...
Nicholas, Tuesday, February 4, 2003, 12:25:08 PM: > 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 try maybe SELECT p.name, p.company FROM contacts c, projects p WHERE CONCAT(c.firstName, " ", c.lastName) != p.name > 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 -- Brian Email: <[EMAIL PROTECTED]> - 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
Re: Opposite selection...
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
