I have two tables:

user

approver

(subordinate_id, leader_id and main_leader_id are foreign keys that correspond to the id column of the user table)

I would like to perform a query that brings all user names that are not admin (user table is_admin=0) and that are active (user table is_active=1), and that if they have the id in the subordinate_id column in the approver table that only brings the name of that user that has the is_active of the approver table = 0.

That is, I would like to bring users that if they have any record as subordinate_id that only bring me those that are not active in the approver table.

I tried to get the data in the following way:

 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 
    AND 
      U.is_active = 1

But with this query i only get the user name that not has a register in the approver table, and in my case i want to get the user that have a register in the approver table as subordinate_id, but not if the register have the column 'is_active' equal to 1.

In my final result I could get something like this:

Alan carl any marry Pedri

CodePudding user response：

In order to make this working, you should split the conditions in the WHERE clause into:

• "user" conditions: is_admin = 0 AND is_active = 1
• "approver" conditions: is not a subordinate OR is_active = 0

These two groups of conditions have to be set in AND.

SELECT DISTINCT user_.id, user_.full_name 
FROM      user_
LEFT JOIN approver
       ON user_.id = approver.subordinate_id
WHERE (user_.is_admin = 0 AND user_.is_active = 1) 
  AND (approver.id IS NULL OR approver.is_active = 0)

Check the demo here.

Note: the DISTINCT keyword is necessary because the JOIN operation is made between two tables having cardinality 1:n.