MySQL: employees without project and project without employees

Question

I want to fetch all the emplyees without project and the project without employees. I don't know where am I doing wrong.

Table structure:

employee: employee_id (PK), employee_name //All employees

project: project_code (PK), project_title //All projects

project_team: project_code (FK), employee_id (FK) //Bridge table for those of projects and employees are in use.

this is my query:

SELECT *
FROM employee e
    LEFT JOIN project_team pt USING (employee_id) WHERE pt.`project_code` IS NULL
    INNER JOIN project p USING (project_code) WHERE (e.`employee_id` IS NULL)

That requires 2 queries. – Abhik Chakraborty Apr 17 '15 at 10:28 — Abhik Chakraborty, Apr 17 '15 at 10:28
I want to do in 1 query @AbhikChakraborty – Imam Bux Apr 17 '15 at 10:34 — Imam Bux, Apr 17 '15 at 10:34
Then use UNION between those 2 queries... – Marki555 Apr 17 '15 at 10:41 — Marki555, Apr 17 '15 at 10:41

mfdev · Answer 1 · 2015-04-17T10:52:13.033

0

You might want something like this:

SELECT employee_id AS id_or_code, employee_name AS name_or_title FROM employee e LEFT JOIN project_team pt ON e.employee_id = pt.employee_id WHERE pt.project_code is NULL

UNION ALL

SELECT project_code AS id_or_code, project_title AS name_or_title FROM project p LEFT JOIN project_team pt ON p.project_code = pt.project_code WHERE pt.employee_id is NULL;

This way you have the query result in two columns with the first column as the id of employee or code or project and the second column as the name of the employee or the title of the project.

edited Apr 17 '15 at 10:52

answered Apr 17 '15 at 10:37

mfdev

196
2
7

Sorry, had misunderstood the requirements, the code now is fixed. – mfdev Apr 17 '15 at 10:50

score 0 · Answer 2 · edited May 23 '17 at 11:43

0

You need FULL JOIN but MySQL don't have it.

Here you can find union solution https://stackoverflow.com/a/4796911/3970250

edited May 23 '17 at 11:43

Community

1
1

answered Apr 17 '15 at 10:39

Alexey Kurilov

438
4
10

score 0 · Answer 3 · answered Apr 17 '15 at 10:44

You are trying to merge 2 different select record into one result set and one way is using union all and then tag each record type something as

select 
e.employee_id as id , 
e.employee_name as name, 
'Employee' as type 
from employee e 
left join project_team pt on pt.employee_id = e.employee_id 
where pt.employee_id is null
union all
select 
p.project_code as id ,
p.project_title as name, 
'Project' as type 
from project p 
left join project_team pt on pt.project_code = p.project_code 
where pt.project_code is null ;

Here is a test case

mysql> select * from employee ;
+-------------+---------------+
| employee_id | employee_name |
+-------------+---------------+
|           1 | A             |
|           2 | B             |
|           3 | C             |
|           4 | D             |
|           5 | E             |
+-------------+---------------+
5 rows in set (0.00 sec)

mysql> select * from project ;
+--------------+---------------+
| project_code | project_title |
+--------------+---------------+
|            1 | P1            |
|            2 | P2            |
|            3 | P3            |
|            4 | P4            |
+--------------+---------------+
4 rows in set (0.00 sec)

mysql> select * from project_team ;
+--------------+--------------+
| project_code | employee_id |
+--------------+--------------+
|            1 |            1 |
|            1 |            2 |
|            2 |            3 |
|            2 |            4 |
|            3 |            2 |
|            3 |            1 |
|            3 |            3 |
+--------------+--------------+

Running the above query will give you as

+------+------+----------+
| id   | name | type     |
+------+------+----------+
|    5 | E    | Employee |
|    4 | P4   | Project  |
+------+------+----------+

MySQL: employees without project and project without employees

3 Answers3