Hello Victor,
At 04:30 PM 10/10/2002 +0100, Victor Kirk wrote:
>>> 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:
select
u.userid
from
users u left join teams t on u.userid=t.userid
where t.userid is null
A sample database dump is below. Also see section 1.4.4.1 (sub-selects) in
the manual. Is that what you were looking for?
Regards,
- Robert
# MySQL dump 8.13
#
# Host: db Database: foo
#--------------------------------------------------------
# Server version 3.23.36
#
# Table structure for table 'teams'
#
CREATE TABLE teams (
teamid int(11) default NULL,
userid int(11) default NULL
) TYPE=MyISAM;
#
# Dumping data for table 'teams'
#
INSERT INTO teams VALUES (1,1);
INSERT INTO teams VALUES (1,2);
INSERT INTO teams VALUES (1,3);
INSERT INTO teams VALUES (2,4);
INSERT INTO teams VALUES (2,5);
INSERT INTO teams VALUES (2,6);
INSERT INTO teams VALUES (3,1);
INSERT INTO teams VALUES (3,3);
INSERT INTO teams VALUES (3,5);
INSERT INTO teams VALUES (3,7);
#
# Table structure for table 'users'
#
CREATE TABLE users (
userid int(11) default NULL
) TYPE=MyISAM;
#
# Dumping data for table 'users'
#
INSERT INTO users VALUES (1);
INSERT INTO users VALUES (2);
INSERT INTO users VALUES (3);
INSERT INTO users VALUES (4);
INSERT INTO users VALUES (5);
INSERT INTO users VALUES (6);
INSERT INTO users VALUES (7);
INSERT INTO users VALUES (8);
INSERT INTO users VALUES (9);
INSERT INTO users VALUES (10);
---------------------------------------------------------------------
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
