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
