By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table games(
name varchar(25),
details_id int);
insert into games values
('Mario Kart',007),
('Ninja Battle',005);
create table details(
details_id int,
locale char(5),
description varchar(50));
insert into details values
(007 ,'en-GB','A nice racing game.'),
(007 ,'es-ES','Un buen juego de carreras.'),
(005,'en-GB','A street fighting game.');
2 rows affected
3 rows affected
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. |