Victor, good shot! I thought of this one in the first place:
SELECT u.user_id FROM user_profile u LEFT JOIN team_member t ON u.user_id = t.user_id WHERE t.team_id <> 2 But unfortunately, it will yield: +---------+---------+ | user_id | team_id | +---------+---------+ | 1000 | 1 | | 1001 | 3 | | 1002 | 3 | | 1003 | 3 | | 1004 | 1 | | 1005 | 1 | | 1005 | 3 | +---------+---------+ 7 rows in set (0.00 sec) Where it should yield: +---------+ | 1002 | | 1004 | +---------+ A subselect would be most simple: SELECT u.user_id FROM user_profile u WHERE user_id NOT IN (SELECT user_id FROM team_member t WHERE t.team_id = 2) It's too late am I'm too tired to rewrite this query so it will work in MySQL <= 4.0.9. With MySQL 4.1 (alpha out now!!!) we have subselects :) Good night, -- Stefan Hinz <[EMAIL PROTECTED]> Geschäftsführer / CEO iConnect GmbH <http://iConnect.de> Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 ----- Original Message ----- From: "Victor Pendleton" <[EMAIL PROTECTED]> To: "'heiko mundle'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, January 28, 2003 5:30 PM Subject: RE: Help with SELECT, JOIN and WHERE query SELECT u.user_id FROM user_profile u LEFT JOIN team_member t ON u.user_id = t.user_id WHERE t.team_id <> 2 -----Original Message----- From: heiko mundle [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 8:08 AM To: [EMAIL PROTECTED] Subject: Help with SELECT, JOIN and WHERE query Hi, I got a difficult problem with a SELECT query: In my application there are two tables, one for all users (user_profile) one for a user-in-team relation. I want to know, which user is still not in a speciffic team. Users can be in more than one team, but we don't care about that. Example: user_profile: +---------+ | user_id | +---------+ | 1000 | | 1001 | | 1002 | | 1003 | | 1004 | | 1005 | | 1006 | | ... | +---------+ team_member: +---------+---------+ | team_id | user_id | +---------+---------+ | 1 | 1000 | | 1 | 1004 | | 1 | 1005 | | 2 | 1006 | | 2 | 1003 | | 2 | 1000 | | 2 | 1001 | | 2 | 1005 | | 3 | 1001 | | 3 | 1005 | | 3 | 1002 | | 3 | 1003 | | ... | ... | +---------+---------+ Who is not in team no. 2? Result: +---------+ | 1002 | | 1004 | +---------+ Thanks for any hint Kind regards Heiko Mundle -- +++ GMX - Mail, Messaging & more http://www.gmx.net +++ NEU: Mit GMX ins Internet. Rund um die Uhr für 1 ct/ Min. surfen! --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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
