Tricky query in DB2-CodePudding

I have a DB2 table with Users and another one with Groups. This both tables are connected by ID.

At the table Groups I got many lines for a single user, like:

I'm trying to know all users from the table Users that are not in the Admin group at the Group table.

I'm doing the following:

SELECT ID FROM USER u
JOIN GROUPS g
ON u.ID = g.ID
WHERE g.GROUP NOT IN ('Admin')

But this query is giving me

ID	Group
Jhonn	Common
Jhonn	RH

How can I make a query to know if the user is not with the Admin group?

CodePudding user response：

Assuming, based on your requirements that Jhonn shouldn't appear:

SELECT ID FROM USER u
JOIN GROUPS g
ON u.ID = g.ID
WHERE u.ID NOT IN (SELECT ID FROM GROUPS WHERE GROUP = 'Admin')

And depending on what columns you need from the tables, you could drop the join.

CodePudding user response：

SELECT ID
FROM USERS U
WHERE NOT EXISTS
(
   SELECT 1 FROM GROUPS G WHERE U.ID=G.ID AND G.GROUP='Admin'
 )

Could you please try this

CodePudding user response：

You can use LEFT OUTER JOIN and test for non-existence of a group:

SELECT u.ID
FROM   USER u
       LEFT OUTER JOIN GROUPS g
       ON (u.ID = g.ID AND g.group = 'Admin')
WHERE  g.id IS NULL;

db<>fiddle here