On 10 Oct 2002 at 16:30, Victor Kirk wrote: > >> I have two tables, one for user details and another to indicate > >> membership of some team. The later has user_id and team_id. I > >> want to select all users that are NOT in a particular team. > >> > >> After a lot of effort (my sql skills are almost no existent) I > >> have the following that works with Oracle. > >> > >> SELECT u.uname FROM users u > >> WHERE NOT EXISTS (SELECT * FROM team_members > >> WHERE team_id = 7 AND user_id=u.user_id); > >> > >> This doesn't work with MySQL :-( > >> > >> Can anyone help me? Ideally I would like something that would > >> be portable to oracle/sql server. Efficiency is not an issue. > > > How about ... > > > SELECT * FROM teams > > LEFT JOIN users USING (team_id) > > WHERE users.team_id <> 7; > > That almost works, unfortunatly a user can belong to many teams. > Thus this gives a row corresponding to each user in another team, > including the users in the team specified. It also does'nt return > users who are'nt in a team at all.
Try this (untested): SELECT u.uname FROM users AS u LEFT JOIN team_members AS t ON (t.team-id = 7 AND t.user_id = u.user_id) WHERE t.team_id IS NULL; -jan --------------------------------------------------------------------- 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
