jeudi 17 septembre 2015

Query to select element joined and grouped including null

I'm trying to join two tables grouping by the first table but retaining the element that are not related to the first table in a "null" group, like this:

First Table

+----+--------+
| id |  name  |
+----+--------+
|  1 | Rome   |
|  2 | Zurich |
|  3 | Paris  |
+----+--------+

Second Table

+----+-----------------------+------+--------+
| id |         name          | city | height |
+----+-----------------------+------+--------+
|  1 | Colosseo              | 1    |     60 |
|  2 | Arc de Triomphe       | 3    |     80 |
|  3 | Tour Eiffel           | 3    |     80 |
|  4 | Empire State Building | null |     90 |
|  5 | Petronas Towers       | null |     90 |
+----+-----------------------+------+--------+

Expected Result

+---------+---------+-------------+
| city.id |  name   | sum(height) |
+---------+---------+-------------+
| 1       | Rome    | 60          |
| 2       | Zurich  | null        |
| 3       | Paris   | 160         |
| null    | No City | 180         |
+---------+---------+-------------+

Right now, the only way I found to do this is to left join the first with the second table and grouping by city.id, then performing a UNION with the city.id null.

There is a way to perform this without UNION and using SQLite?

Aucun commentaire:

Enregistrer un commentaire