SQL got me confused

[timhisu]

Here's the basic situation. I got a table of users (id, username, firstname, lastname), a table of offices (id, name), and a list of users in particular offices (id, office_id, user_id) since a user can be in any number of offices.

So, in my user-add script, I'm creating a select box with a list of users to choose from to add to a particular office. Ideally, I want the list of users to be JUST the users that aren't already in the office.

The SQL to grab the users that ARE in a particular office is easy:

SELECT u.id, u.f_name, u.l_name
FROM users as u, office-users as o
WHERE o.office_id = [requested office]
AND u.id = o.user_id

But, the SQL to grab the users that are NOT in a particular office is much more tricky to me. Does anyone have any idea how to accomplish this?

[TheNige]

Something like this should work.

SELECT id, f_name, l_name
FROM users,
WHERE id not in (select user_id from office-users where office_id = [requested office])

[timhisu]

ahh, the sub-select. Thanks mate