sql query that depends on each value of other table

user_15196067 0
Viewed 80

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.

postgresql sql
1 Answers

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

Admin1
Related