SQL

[JonD]

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)

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

Any ideas?

 

[toString]

Hate that php code box...

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

Might work.... :/

 

[JonD]

ah didnt know about that keywords, i knew there was sumthing around that did what i wanted, seems to work so far.

Thanks

 

[feenster99]

Make sure you have MySQL version 4.1 or better if you want to run subqueries

Matt

 

[arty]

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

 

[JonD]

Hate that php code box...

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

Might work.... :/

Worked perfectly

Thx