ppardoz ppardoz - 4 months ago 9
SQL Question

Left join query with all possible combinations

I have two tables:

languages, as an index of my languages.

isolanguage
"de"
"en"
"es"
"fi"
"fr"
"no"
"pl"
"ru"


And translations, where I store translations of places:

Name, translation, isolanguage
"Umeå Airport";"Flughafen Umeå";"de"
"Umeå Airport";"Umeå Airport";"en"
"Umeå Airport";"Aeropuerto de Umeå";"es"
"Umeå Airport";"Uumajan lentoasema";"fi"


what I'm trying to get is a list of all elements in translations table for each language in language table.

Expected result:

"Umeå Airport";"Flughafen Umeå";"de"
"Umeå Airport";"Umeå Airport";"en"
"Umeå Airport";"Aeropuerto de Umeå";"es"
"Umeå Airport";"Uumajan lentoasema";"fi"
"Umeå Airport";NULL;"fr"
"Umeå Airport";NULL;"no"
"Umeå Airport";NULL;"pl"
"Umeå Airport";NULL;"ru"


The point is that I need all languages, having translation or not.

I'm trying a left join query, but is not working, it's only showing elements which are in both tables (excluding NULLS)

select * from languages a
left join translations b
on (a.isolanguage = b.isolanguage)

"Umeå Airport";"Flughafen Umeå";"de"
"Umeå Airport";"Umeå Airport";"en"
"Umeå Airport";"Aeropuerto de Umeå";"es"
"Umeå Airport";"Uumajan lentoasema";"fi"
NULL;NULL;"fr"
NULL;NULL;"no"
NULL;NULL;"pl"
NULL;NULL;"ru"

Answer

You can use this:

select name, isolanguage, translation
from (
    select distinct t.name, l.isolanguage
    from translations t
    cross join languages l
    ) t
left join translations
using (name, isolanguage)
order by 1, 2;

     name     | isolanguage |    translation     
--------------+-------------+--------------------
 Umea Airport | de          | Flughafen Umea
 Umea Airport | en          | Umea Airport
 Umea Airport | es          | Aeropuerto de Umea
 Umea Airport | fi          | Uumajan lentoasema
 Umea Airport | fr          | 
 Umea Airport | no          | 
 Umea Airport | pl          | 
 Umea Airport | ru          | 
(8 rows)