Join Problem

ChetUbetcha

I can't figure out which JOIN I need to use.

Tables
users - id : admin_level
assigned - user_id : admin_level

Once I assign a user and they are INSERTed into the assigned table, I need to be able to pull up a list of available users that have not been assigned yet.

If I assign user id 1 with admin level 1 and there are still 5 other users with the same admin_level, I want to click Assign again, but only pull up the five that have not been assigned yet.

I've tried things like:

SELECT * FROM users JOIN assigned ON (users.id != assigned.user_id) WHERE admin_level = '1'

and a whole bunch of other garbage like that. I tried some others, but when I emptied the assigned table, the queries would not work no matter what I tried.

reddrum

I think a right join may work, that way you get all the records from users.
my sql is not that good so this may need some tweeking.

"SELECT users.id, assigned.user_id FROM assigned.user_id RIGHT JOIN users ON assigned.user_id = users.id WHERE assigned.user_id Is NULL"

tomhath

Or use a subselect if your version of MySql supports it:

select * from users
where not exists (select 1 from assigned where assigned.user_id = users.id)

Piranha

I would use an outer right join:

SELECT * FROM users OUTER RIGHT JOIN assigned ON (users.id = assigned.user_id) WHERE admin_level = '1'

A tip is to avoid subqueries and IN. Queries tend to be much slower when one of those are used. That's why it is normally better to use joins.

tomhath

"IN" will often end up as a slow query. I've never had a problem with EXISTS. In fact, the execution plan is often exactly the same as the same query with a JOIN on MSSQL. MySql might be a different, never hurts to try both ways.