CODEWITHSUNDEEP
mysqlsql
Chud37
Chud37

Reputation: 5017

Exclude Staff Members

I have a user table:

id, userID, name, postcode
2   99      Bob   AAA BBB
3   8384    Jim   CCC DDD

And I have a user-keys table:

id, userID, keyID, val
1   435     3      1
2   773     8      0
3   99      2      1
4   99      5      1
5   99      2      1

Where keyID = 2 indicates that it is a staff member. I want to get all the userIDs from user table that are not staff members. So far I have the following SQL:

SELECT u.`userID` FROM `users` u
WHERE u.`userID` IS NOT NULL AND u.`userID` != 0;

However this does not exclude all users that have an entry in user-keys table with a keyID value of 2 and val of 1.

How can I perform this?

Edit: I neglected to state that any user may have multiple entries in the user-keys table, and I just want one list of userID from the user table, So really the results I am looking for from the above tables would simply be 8384.

Upvotes: 1

Views: 64

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521914

You can try the following join query:

SELECT
    t1.userID
FROM user t1
LEFT JOIN
(
    SELECT DISTINCT userID, keyID        -- you don't need DISTINCT here, but it may help
    FROM `user-keys`                     -- and it makes the logic a bit clearer
    WHERE keyID = 2
) t2
    ON t1.userID = t2.userID
WHERE t2.userID IS NULL

The logic used here is that we retain all users from the user table who do not appear in user-keys with a keyID of 2, or who do not appear in user-keys at all.

Follow the link below for a demo in MySQL. Note that in some databases user-keys would not be a valid table name and would need to be escaped (I escaped above using backticks, SQL Server would use [user-keys], etc.).

Rextester

Update:

If you want to find only staff members, then the logic is much simpler. In this case, we can just INNER JOIN the two tables together and check the status of each user:

SELECT DISTINCT
    t1.userID
FROM user t1
INNER JOIN `user-keys` t2
    ON t1.userID = t2.userID
WHERE t2.keyID = 2

Upvotes: 1

Jim Macaulay
Jim Macaulay

Reputation: 5155


You can use below code

SELECT * FROM USER WHERE ID IN 
(SELECT ID FROM USER_KEY WHERE KEY_ID <> 2);

If you need all columns you can use,

SELECT * FROM USER U
INNER JOIN USER_KEY  UK
ON(U.ID = UK.ID)
WHERE UK.KEYID <> 2;

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

If you want the user with keyID <> 2 you should use a inner join 

  SELECT u.userID 
  FROM users u
  and u.userID  not in (select userID
    from `user-keys` 
  WHERE  keyID <>2 )

Upvotes: 1

Related Questions