I have following three tables:
dbo.Product Id | Name 1 | P1 2 | P2 dbo.ProductTranslations Id | LangId | Text 1 | 1 | Text_EN 1 | 2 | Text_DE 2 | 1 | OtherText_EN dbo.Language Id | Culture 1 | en-US 2 | de-DE 3 | es-ES
I need a query like:
select * from ProductTranslations pt (some join) Language lang
To produce: for product 1:
1 | en-US | Text_EN 1 | de-DE | Text_DE 1 | es-ES | null
for product 2:
2 | en-US | OtherText_EN 2 | de-DE | null 2 | es-ES | null
Ideally I would like to have a view that I can filter on using Id. Any help would be appreciated.
I see. You want rows for all the languages, even when there is no matching translation.
You can do this with
select p.productId, l.culture, pt.text from (select 1 as productId) p cross join language l left join ProductTranslations pt on pt.id = p.productId and pt.langid = l.id;
You can just change the “1” to whatever product you like.
If you prefer to use a
where clause, you can do:
select p.productId, l.culture, pt.text from product p cross join language l left join ProductTranslations pt on pt.id = p.productId and pt.langid = l.id where p.id = 1;