>> 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. > (Look in the manual under Sub Selects for subquery workarounds.) Thanks, I solved another problem I had by looking there, but saw nothing to help me here. Vic -- This message, including attachments, is intended only for the use by the person(s) to whom it is addressed. It may contain information which is privileged and confidential. Copying or use by anybody else is not authorised. If you are not the intended recipient, please contact the sender as soon as possible. The views expressed in this communication may not necessarily be the views held by Serco Integrated Transport. --------------------------------------------------------------------- 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
