Need help with a tricky query, 3 tables involved

[cutley]

Okay, I'm working with PHP and MySQL.

I have 3 tables I'm working with: a Users table, a Sources table, and a Permissions table.

Sources have a one to many relationship with the Permissions table. Each entry is just a source id (links to source_id in Sources table) plus a user id (links to user_id in Users table).

So I need to select all the Sources, and generate a list box of all users that do not already have an entry in the Permissions table, or in others words all users that have not been granted permission for the particular source.

I think I got pretty close to making this work, but the logic and necessay SQL is scrambling my brain. I'm new to SQL so any help you can provide would be appreciated!

Cheers,
CJ

 

[Zathras]

I'm not sure I understood your table descriptions correctly, but here is a script you can use for exploration:

CREATE TABLE #USERS (USERID INT, USERNAME VARCHAR(30))
CREATE TABLE #SOURCES (SOURCEID INT, SOURCENAME VARCHAR(30))
CREATE TABLE #PERMISSIONS (SOURCEID INT, USERID INT)
INSERT INTO #USERS VALUES(1,'BROWN')
INSERT INTO #USERS VALUES(2,'GREEN')
INSERT INTO #USERS VALUES(3,'BLACK')
INSERT INTO #SOURCES VALUES(100,'SOURCE A')
INSERT INTO #SOURCES VALUES(200,'SOURCE B')
INSERT INTO #SOURCES VALUES(300,'SOURCE C')
INSERT INTO #PERMISSIONS VALUES(100,1)
INSERT INTO #PERMISSIONS VALUES(200,1)
INSERT INTO #PERMISSIONS VALUES(100,2)
INSERT INTO #PERMISSIONS VALUES(300,2)

SELECT USERNAME FROM #USERS WHERE USERID NOT IN
(SELECT DISTINCT USERID FROM #SOURCES S
JOIN #PERMISSIONS P ON S.SOURCEID=P.SOURCEID)
GO
DROP TABLE #USERS
DROP TABLE #SOURCES
DROP TABLE #PERMISSIONS
GO

BTW, USER_NAME and USER_ID may be reserved words in some DBMS. In particular, they are function names in SQL Server. Best not to use them with the underscores.

 

[cutley]

Thanks very much for the reply, I think I confused matters. Let me see if I can clear it up some... I have 3 tables:

USERS
emp_id (unique ID)
f_name
l_name

SRC_MAIN
src_id (unique ID)

SRC_RESTRICT_EMP
src_restrict_id
src_id (many to one link to src_id in SRC_MAIN)
emp_id (many to one link to emp_id in USERS)


So... When we want to grant "permissions" for a user to access a source in SRC_MAIN, we create an entry in SRC_RESTRICT_EMP that includes the USERS emp_id and the SRC_MAIN src_id.

So I have a screen for an indvidual source, and I have to print a list of all USERS entries that DO NOT have a corresponding entry in SRC_RESTRICT_EMP for the src_id I happen to be viewing at the time.

In other words...

select all users where emp_id and THIS src_id not in src_restrict_emp

Does this help at all?

Thanks again!

CJ

 

[BillDHS]

How about a subquery?
SELECT Employ_ID
FROM Users
WHERE Employ_ID <>
(Select Employ_ID
From SRC_RESTRICT_EMP)
;

Use the valid NOT EQUALS (<>) code for your software.

 

[JamesLean]

This query should get you what you need:

SELECT f_name, l_name
FROM users
WHERE emp_id NOT IN (SELECT emp_id FROM src_restrict_emp WHERE src_id = 123)

 

[swampBoogie]

Mysql does not support subqueries.

select f_name,l_name from users left outer join SRC_RESTRICT_EMP
on users.emp_id = SRC_RESTRICT_EMP.emp_id
and src_id = 4711
where SRC_RESTRICT_EMP.emp_id is null