MySQL Join Problem

[worldcommander]

I'm having trouble with a MySQL query. Here's the basic table structure:

#users
id
name
password
status

#access
id
user_id
module

The table 'users' is where all general admin user info is stored. Access to a module is granted by adding the appropriate user info into the 'access' table. I need to provide a list to the module superuser of all users in the 'user' table that are of status '1' and are not currently assigned access to their module. The idea is that they'll be able to add access (click, select, whatever) to existing admin users from a list of available admin users, but the list won't display people who already have access.

This is closest thing I have that will work...sort of:

[code=php]SELECT users.*
FROM users, access
WHERE users.status = '1'
AND access.user_id = users.id
AND access.module != $module[/code]

(the variable $module is a session value that is set add login.)

The obvious problem here is that for this work the user must exist in the 'access' table. I've tried "AND access.user_id != inc_users.id" and I get no results.

Any help would be awesome!

[ultraslacker]

OUTER JOINs

SELECT users.*
FROM users 
LEFT OUTER JOIN access
ON users.id = access.user_id
WHERE users.status = '1'
AND access.module != $module

[worldcommander]

That actually gives me the same results as I get with the query I already tried.

As a test I have one user that is not in the access table, and is a status 1. The query should show him.

[ultraslacker]

AND access.module != $module

Oops.

SELECT users.*
FROM users
LEFT OUTER JOIN access
ON (users.id = access.user_id and access.module=$module)
WHERE users.status = 1
AND access.module IS NULL