I have two tables, user:

approver :

and I would like to perform a query that brings up the user names that do not have the id in the subordinate_id column of the approvers table.

I tried it this way:

SELECT 
    full_name
FROM user AS U 
WHERE NOT EXISTS(
SELECT * FROM approver AS A 
WHERE A.subordinate_id = U.id AND U.is_admin = 0);

but in this case the admin user is still coming, and I would like to not bring whoever has the is_admin column of the usuario table = 1. I want to bring only common users and not admin.

Can someone help me with this?

CodePudding user response：

Try this query

SELECT full_name FROM user AS U 
left join approver as A on U.id = a.subordinate_id 
where A.id is null AND U.is_admin = 0

CodePudding user response：

You need to put AND U.is_admin = 0 outside EXISTS

create table `user`(
 id int,
 full_name varchar(20),
 is_admin int
);

create table `approver`(
 id int,
 subordinate_id int,
 approver_id int
);

insert into `user`(id,full_name,is_admin) values
(1,'jane',0),
(2,'Helio',0),
(3,'fran',0),
(4,'mila',0),
(5,'admin',1);

insert into `approver`(id,subordinate_id,approver_id) values
(1,1,2),
(2,3,4);

SELECT 
    full_name
FROM user AS U 
 WHERE NOT EXISTS(SELECT A.* FROM approver AS A WHERE A.subordinate_id = U.id )
AND U.is_admin = 0

DB Fiddle Demo