The SQL code below is parameterized with a desired translation ('fr' in this case) and returns desired translations where available, falls back to 'en' if desired translation doesn't exist, and further falls back to original text if no alternate 'en' translation is defined.
SELECT
m.MaterialID AS 'ID'
,COALESCE( (SELECT Translation FROM Translation t WHERE (t.TextContentID = m.NameFullTextID AND t.LanguageCode = 'fr'))
, (SELECT Translation FROM Translation t WHERE (t.TextContentID = m.NameFullTextID AND t.LanguageCode = 'en'))
, (SELECT OriginalText FROM TextContent t WHERE (t.TextContentID = m.NameFullTextID))
) AS 'Full Name'
,COALESCE( (SELECT Translation FROM Translation t WHERE (t.TextContentID = m.NameShortTextID AND t.LanguageCode = 'fr'))
, (SELECT Translation FROM Translation t WHERE (t.TextContentID = m.NameShortTextID AND t.LanguageCode = 'en'))
, (SELECT OriginalText FROM TextContent t WHERE (t.TextContentID = m.NameShortTextID))
) AS 'Short Name'
,COALESCE( (SELECT Translation FROM Translation t WHERE (t.TextContentID = m.MaterialTypeID AND t.LanguageCode = 'fr'))
, (SELECT Translation FROM Translation t WHERE (t.TextContentID = m.MaterialTypeID AND t.LanguageCode = 'en'))
, (SELECT OriginalText FROM TextContent t WHERE (t.TextContentID = m.MaterialTypeID))
) AS 'Type'
,m.Density
,m.StockCode AS 'Stock Code'
FROM Material m
I haven't spent a lot of time in SQL and am wondering if this is the best way to accomplish the task. Recommendations to simplify, clarify, or otherwise improve are welcome!