CODEWITHSUNDEEP
mysqlsqldatabaserelational-database
user3047641
user3047641

Reputation: 149

Using SQL, how do I write a query that returns a null if there are no entries, but lists all entries if any exist?

Here's an example of what I mean. Imagine you have a database with a table in it that has people and the cars they own. Some people own more than one car, some people own no cars at all. So your table might look something like this:

+----+---------------+-------------+------------+
| id | name          | car_id      |department  |
+----+---------------+-------------+------------+
|  1 | John Smith    |           0 |          1 |
|  2 | Josey Wales   |           1 |          1 |
|  3 | Sally Thomas  |           2 |          2 |
|  3 | Bob McClain   |        null |          2 |
|  4 | Peter Sellers |        null |          3 |
+----+---------------+-------------+------------+

I need to write a query that would return the department number and all car IDs that belong to someone in that department. The tricky part is that if there are no cars in the department, you should still list the department number followed by null. But if there are cars, then you don't need to list null at all even if there are employees with a null entry in their car id. So the output would be something like this.

+------------+-----------+
|department  | car_id    |
+------------+-----------+
|          1 |         0 |
|          1 |         1 |
|          2 |         2 |
|          3 |      null |
+------------+-----------+

Note that the result should NOT be this -

+------------+-----------+
|department  | car_id    |
+------------+-----------+
|          1 |         0 |
|          1 |         1 |
|          2 |         2 |
|          2 |      null |
|          3 |      null |
+------------+-----------+

or this

+------------+-----------+
|department  | car_id    |
+------------+-----------+
|          1 |         0 |
|          1 |         1 |
|          2 |         2 |
+------------+-----------+

I'm having trouble figuring out how to get the null if there are no car values, but ignore it if there's at least one car that can be listed. I'm kind of a beginner to SQL and I really appreciate your help! Also, if it matters, I'm using MySQL.

Upvotes: 0

Views: 62

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

This can be done with union by first getting all the non null car id's and getting the null car_id's if the department wasn't selected previously.

SQL Fiddle

select department,car_id
from t
where car_id is not null
union all --use union to get 1 null row per department in case there are multiple nulls
select department,car_id
from t t1
where car_id is null and 
not exists (select 1
            from t where t1.department=department and car_id is not null)

The query can also be simplified to 

select distinct department,car_id
from t t1
where car_id is not null
or (car_id is null 
    and not exists (select 1
            from t where t1.department=department and car_id is not null)
    )

Upvotes: 0

Related Questions