I'm not used to query sentences and this one is a bit tricky, I don't know even if it is possible (at least with just a single sql query) or if I should change the question name to something more accurated, if so let me know.
I have two tables, I will make them simple. The first one that is about games has a name and a details_id
, the other on that consists on the game details has details_id
(foreign key), locale
and description
.
Games |
---|
name |
details_id |
Details |
---|
details_id |
locale |
description |
A game can have multiple details associated as the details_id
is not unique. So I can have 3 Details tables with same id associated to a Game but there is no Game with the same details_id
as other. I will make an example:
GAMES
name | details_id |
---|---|
Mario Kart | 007 |
DETAILS
details_id | locale | description |
---|---|---|
007 | en-GB | A nice racing game. |
details_id | locale | Description |
---|---|---|
007 | es-ES | Un buen juego de carreras. |
I show all the Games and their english descriptions in a view, they are now sorted by description as follows:
SELECT Games.name, Details.description
FROM Games
INNER JOIN Details ON Details.locale='en-GB'
The trick is that I want it to depend on the locale, they might be games not translated to the same locale as others.
The goal is a select that through a join, can select all the Game names and Detail descriptions ordered by description
where locale is es-ES
, and if the detail does not exist, it will take the en-GB
one still ordering alphabetically among spanish descriptions.
CodePudding user response:
Here I have added a second game which only has an english description. We join onto the details table twice, once to extract the english descriptions and once to extract the spanish descriptions. We use the function COALESCE which returns the first value which is not null, ie. the spanish if there is and the english if there is no spanish .
Please see the dbFiddle link at the bottom for the schema and to test further.
select * from games; select * from details;
name | details_id :----------- | ---------: Mario Kart | 7 Ninja Battle | 5 details_id | locale | description ---------: | :----- | :------------------------- 7 | en-GB | A nice racing game. 7 | es-ES | Un buen juego de carreras. 5 | en-GB | A street fighting game.
select name, coalesce(esp.description, eng.description) Description from games g left join (select details_id, description from details where locale = 'en-GB') eng on g.details_id = eng.details_id left join (select details_id, description from details where locale = 'es-ES') esp on g.details_id = esp.details_id
name | description :----------- | :------------------------- Mario Kart | Un buen juego de carreras. Ninja Battle | A street fighting game.
db<>fiddle here