CODEWITHSUNDEEP
sqlpostgresqljoinleft-join
Pablo Pardo
Pablo Pardo

Reputation: 739

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"

Upvotes: 0

Views: 1056

Answers (2)

klin
klin

Reputation: 121804

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)

Upvotes: 2

EmerG
EmerG

Reputation: 1046

U can use 

SELECT Name, translation, isolanguage
FROM 
translations
WHERE
language.isolanguage=translation.isolanguage AND
language.isolanguage = 'de' OR
language.isolanguage = 'fr' OR .......

Upvotes: 1

Related Questions