Employees With Missing Information Problem


Description

LeetCode Problem 1965.

Table: Employees

1
2
3
4
5
6
7
8
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| name        | varchar |
+-------------+---------+
employee_id is the primary key for this table.
Each row of this table indicates the name of the employee whose ID is employee_id.

Table: Salaries

1
2
3
4
5
6
7
8
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| salary      | int     |
+-------------+---------+
employee_id is the primary key for this table.
Each row of this table indicates the salary of the employee whose ID is employee_id.

Write an SQL query to report the IDs of all the employees with missing information. The information of an employee is missing if:

  • The employee’s name is missing, or
  • The employee’s salary is missing.

Return the result table ordered by employee_id in ascending order.

The query result format is in the following example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Employees table:
+-------------+----------+
| employee_id | name     |
+-------------+----------+
| 2           | Crew     |
| 4           | Haven    |
| 5           | Kristian |
+-------------+----------+
Salaries table:
+-------------+--------+
| employee_id | salary |
+-------------+--------+
| 5           | 76071  |
| 1           | 22517  |
| 4           | 63539  |
+-------------+--------+

Result table:
+-------------+
| employee_id |
+-------------+
| 1           |
| 2           |
+-------------+

Employees 1, 2, 4, and 5 are working at this company.
The name of employee 1 is missing.
The salary of employee 2 is missing.


MySQL Solution

1
2
3
4
5
6
7
select employee_id 
from (select employee_id from Employees 
     union all
     select employee_id from Salaries) temp
group by 1
having count(*) = 1
order by 1




Related Posts

Users That Actively Request Confirmation Messages Problem

LeetCode 1939. Write an SQL query to find the IDs...

Strong Friendship Problem

LeetCode 1949. Write an SQL query to find all the...

Leetcodify Similar Friends Problem

LeetCode 1919. Write an SQL query to report the similar...

Employees With Missing Information Problem

LeetCode 1965. Write an SQL query to report the IDs...

Confirmation Rate Problem

LeetCode 1934. Write an SQL query to find the confirmation...

All the Pairs With the Maximum Number of Common Followers Problem

LeetCode 1951. Write an SQL query to find all the...