How can I do a join that takes the two tables and get the table in result. Having trouble thinking about it because one is a horizontal table and the other is a vertical table I believe. The other answers on SO are not clear to me because I have to join a value in a row with a column name. How can I do that?

CREATE TABLE forecast (
   year integer,
   week integer,
   model varchar(50),
   category varchar(50),
   subcategory varchar(50)
);  
insert into forecast (year, week, model, category, subcategory) values (2021, 1, 'AAA', 'CategoryA', 'SubcategoryA');
insert into forecast (year, week, model, category, subcategory) values (2021, 1, 'BBB', 'CategoryA', 'SubcategoryA');
insert into forecast (year, week, model, category, subcategory) values (2021, 1, 'CCC', 'CategoryB', 'SubcategoryB');
insert into forecast (year, week, model, category, subcategory) values (2021, 1, 'DDD', 'CategoryA', 'SubcategoryC');

CREATE TABLE translation (
    type varchar(50),
    name varchar(50),
    translated varchar(50)
); 
insert into translation (type, name, translated) values ('category', 'CategoryA', 'TranslatedCategoryA');
insert into translation (type, name, translated) values ('category', 'CategoryB', 'TranslatedCategoryB');
insert into translation (type, name, translated) values ('subcategory', 'SubcategoryA', 'TranslatedSubcategoryA');
insert into translation (type, name, translated) values ('subcategory', 'SubcategoryB', 'TranslatedSubcategoryB');
insert into translation (type, name, translated) values ('subcategory', 'SubcategoryC', 'TranslatedSubcategoryC');



CREATE TABLE result (
    year integer,
    week integer,
    model varchar(50),
    category varchar(50),
    subcategory varchar(50)
); 


insert into result (year, week, model, category, subcategory) values (2021, 1, 'AAA', 'TranslatedCategoryA', 'TranslatedSubcategoryA');
insert into result (year, week, model, category, subcategory) values (2021, 1, 'BBB', 'TranslatedCategoryA', 'TranslatedSubcategoryA');
insert into result (year, week, model, category, subcategory) values (2021, 1, 'CCC', 'TranslatedCategoryB', 'TranslatedSubcategoryB');
insert into result (year, week, model, category, subcategory) values (2021, 1, 'DDD', 'TranslatedCategoryA', 'TranslatedSubcategoryC');

This

select * from forecast f
left join translation t
on t.name = f.category or t.name = f.subcategory

translates one at a time which makes sense, but I can't get two columns out of it that translate each column

CodePudding user response：

Double left join on the type and the name.
And a coalesce to default to the original name if there's no translation.

select f.year, f.week, f.model
, coalesce(cat.translated, f.category) as category 
, coalesce(subcat.translated, f.subcategory) as subcategory
from forecast f
left join translation cat 
  on cat.name = f.category 
 and cat.type = 'category' 
left join translation subcat 
  on subcat.name = f.subcategory
 and subcat.type = 'subcategory'
order by f.year, f.week, f.model;

db<>fiddle here

CodePudding user response：

We have to do a multiple join. One for each column.

select f.year, f.week, f.model, t.translated as 'category', t2.translated as 'subcategory' 
from forecast f
left join translation t
on t.name = f.category 
left join translation t2
on t2.name = f.subcategory