2019-06-13
Dont know how to form a query in a propper way
源自: stackoverflow
问题

I want to set a query which asks "show me all people from table users which are not found in table employees". Im looking for unemployed people

SELECT user.name,user.surname,user.user_id,employee.employee_id,employee.user_id
FROM user
INNER JOIN employee ON employee.user_id=user.user_id
WHERE user.user_id <> employee.user_id

Tables are linked via user.user_id --> employee.user_id

答案
1

You can do this sort of query using an outer join, and checking for a null value in the outer table.

Here is a slight modification of your query, changing it to an outer join, and looking for a null value being returned.

Edit: I've also added in a third table, containing the manager table, as per the comment to this answer.

SELECT 
    user.name,user.surname,user.user_id,employee.employee_id,employee.user_id 
FROM 
    user 
    LEFT OUTER JOIN employee 
        ON employee.user_id=user.user_id 
    LEFT OUTER JOIN manager
        ON manager.user_id=user.user_id 
WHERE 
    -- The columns from employee and manager get returned with null values if there is no match
    employee.user_id is null 
    and manager.user_id is null 
Dont know how to form a query in a propper way
查看原网页