T-SQL View to select FULL OUTER JOIN and add additional rows on "A" based on "B" table

Question

Hi, I hope someone can help me.

I need to solve a problem with a view that get me the correct result based in the following scenario.

I have two tables: Language and ReportDescription

Language

Id   Tag  Description  
---- ---- ----------------
1    en   English     
2    de   German  
3    fr   French  
4    it   Italian  

ReportDescription

LanguageId ReportId    Description  
---------- ----------- -------------------
1          1           Committee of (Eng)  
2          1           Committee of (German)  
3          1           Committee of (French)  
4          1           Committee of (Ita)  
1          2           Appointment of (Eng)  

The result I'd like to have it would be:

LanguageId ReportId    Description  
---------- ----------- -------------------
1          1           Committee of (Eng)  
2          1           Committee of (German)  
3          1           Committee of (French)  
4          1           Committee of (Ita)  
1          2           Appointment of (Eng)  
2          2           Appointment of (Eng)  
3          2           Appointment of (Eng)  
4          2           Appointment of (Eng)  

Any help will be very appreciated.

Answer 1

This one returns the result you describe, hope that's what you want.

It will take a FallbackDescription if this entry does not exist in ReportDescription.

SELECT
  l.id LanguageId, r.ReportId,
  COALESCE( rd.Description, r.FallbackDescription ) Description
FROM language l
JOIN ( SELECT ReportId, MIN(Description) FallbackDescription
       FROM ReportDescription
       GROUP BY ReportId ) r ON ( 1 = 1 )
LEFT JOIN ReportDescription rd ON ( rd.Id = l.Id AND rd.ReportId = r.ReportId )
ORDER BY ReportId, LanguageId

Answer 2

If what you are looking for is a default Report when not available in especific language, this query will do it:

SELECT rd.LanguageId, rd.ReportId, 
 COALESCE(r.Description, 
   (SELECT Description FROM ReportDescription
    WHERE ReportDescription.ReportId = rd.ReportId AND LanguageId = 1)) AS Description
FROM (SELECT DISTINCT ReportId, l.Id AS LanguageId FROM ReportDescription, Language l) rd
LEFT JOIN ReportDescription r ON rd.LanguageId = r.LanguageId AND rd.ReportId = r.ReportId
ORDER BY rd.ReportId, rd.LanguageId

The default language seems to be english in your question (do not forget to add spanish support ;-) ).