Get only users who do not have the id equal to a condition (SQL)-CodePudding

I have two tables, user:

id	full_name
1	Beatriz
2	Mauro
3	Jose
4	fran

approver :

id	subordinate_id	approver_id
1	1	2
2	3	4

I would like to bring up the names of people who are not registered in the subordinate_id column

I did the following query:

SELECT 
    U.full_name
FROM user AS U
INNER JOIN approver as A
ON U.id <> A.subordinate_id ;

enter image description here

and still users are coming in that are in the subordinate_id column of the approver table. I would like to get the result only for user names that are not subordinate_id, can someone help with this?

I would like a result with only the users that are not subordinate_id

CodePudding user response：

This is simple to accomplish an ansi-sql with a not exists semi join:

Select full_name
from user u
where not exists (
  select * from approver a
  where a.approver_id = u.id <-- or (subordinate_id, whichever it should be)
);