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