Kind of cross join query with filtering

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.

Answer

I see. You want rows for all the languages, even when there is no matching translation.

You can do this with left join:

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;