i appologize for sending unnecessary mail, but after hurrying off that last response, i realized that you are running oracle.
please be aware that you have not encountered a shortcoming in mysql. i refer you to the section in corereader's documentation concerning oracle's non- standard characteristics and behaviours. http://corereader.com/document.htm/ although i'm not familiar with all versions of oracle, oracle has trouble with joins. to create a truly portable statement, out of deference to oracle's shortcomings, you need to use an equal join for this select. try this form: SELECT user.name FROM teams where team.team_id = users.team_id and users.team_id <> 7; although i created the algorithms in corereader, i don't write freehand sql, but i think that you'll find that this statement will run on all major extant servers. again, i strongly recommend that you get corereader. > Hi, > > 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. > > > 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. > -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.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
