SQL

Associate
Joined
26 Jun 2003
Posts
1,140
Location
North West
I have two tables:
Users (User table)
Access (Access table for website access rights)

I need to select all the users from the users table that arnt in the access table.

both tables have userid has thier unique field.

I thought this would work but its coming up with errors: (When the access table is empty and when the access table has more than 1 row in it)

PHP:
$query = mysql_query("SELECT userid, username FROM ".TABLE_PREFIX."users WHERE userid != (SELECT userid FROM ".TABLE_PREFIX."access)");

Any ideas?
 
Soldato
Joined
18 Oct 2002
Posts
8,997
Location
London
Hate that php code box...

Code:
SELECT userid, username 
FROM ".TABLE_PREFIX."users 
WHERE userid not in (SELECT userid FROM ".TABLE_PREFIX."access)

Might work.... :/
 
Soldato
Joined
18 Oct 2002
Posts
6,757
Location
Cambridge
Code:
SELECT u.userid, u.username 
FROM ".TABLE_PREFIX."users u
LEFT OUTER JOIN ".TABLE_PREFIX."access a ON u.userid = a.userid
WHERE a.userid IS NULL

That might work? I'd always try to avoid subqueries if possible :)

arty
 
Associate
OP
Joined
26 Jun 2003
Posts
1,140
Location
North West
KingAdora said:
Hate that php code box...

Code:
SELECT userid, username 
FROM ".TABLE_PREFIX."users 
WHERE userid not in (SELECT userid FROM ".TABLE_PREFIX."access)

Might work.... :/

Worked perfectly

Thx
 
Back
Top Bottom