Question 21982 in Mysql Question posted in
The official MySQL documentation can be found here.

Get only users that do not have the id equal to a condition and are admin(SQL) – Mysql

I have two tables, user:

id full_name is_admin
1 jane 0
2 Helio 0
3 fran 0
4 mila 0
5 admin 1

approver :

id subordinate_id approver_id
1 1 2
2 3 4

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?

Tags:

3

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
  3. 0

    You can try this query. We can select the admin="0" in the first table the can left join on second table. And after that We can do the inverse of inner join to get the results you are expecting.

    SELECT fullname
FROM
(SELECT * FROM user WHERE is_admin="0") U
LEFT JOIN Approver A
ON U.id=A.subordinate_id
WHERE A.subordinate_id IS NULL;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search