SQL select latest record revisions in one of three language supplied in order of preference.

Question

So, I've got a table structure in SQL Server 2005 that has the following composite primary keys:

• Id int
• Culture char(5) (eg: en-US, en-GB etc...)
• Created datetime

The User table has PrimaryCulture and SecondaryCulture columns representing the users language preferences. The Application table contains a single Culture column representing the default language for the app.

I want a statement that will give me the latest revisions in one of the users choice of languages if available and the application default if not. I'm thinking along the lines of nested COALESCE statements eg: COALESCE(CulturePrimary, COALESCE(CultureSecondary, CultureDefault)).

This is what I've tried so far (it doesn't work, but I can't see why not):

SELECT
    d.Id,
    COALESCE(p.Title, COALESCE(s.Title, d.Title)) Title,
    COALESCE(p.[Description], COALESCE(s.[Description], d.[Description])) [Description]
FROM (
    SELECT
        ROW_NUMBER() OVER (ORDER BY Created DESC) Row,
        Id,
        Title,
        [Description]
    FROM Project WHERE Culture = 'en-US') d --DefaultCulture
LEFT OUTER JOIN (
    SELECT
        ROW_NUMBER() OVER (ORDER BY Created DESC) Row,
        Id, 
        Title,
        [Description]
    FROM Project WHERE Culture = 'en-AU') p --PrimaryCulture
    ON p.Id = d.Id
LEFT OUTER JOIN (
    SELECT
        ROW_NUMBER() OVER (ORDER BY Created DESC) Row,
        Id, 
        Title,
        [Description]
    FROM Project WHERE Culture = 'en-GB') s --SecondaryCulture
    ON s.Id = d.Id
WHERE d.Row = 1
AND p.Row = 1
AND s.Row = 1

UPDATE:

So thanks @Martin, the problem was the final WHERE clause. Here's the solution:

SELECT
    x.Id,
    COALESCE(p.Title, s.Title, d.Title) Title,
    COALESCE(p.Description, s.Description, d.Description) [Description],
    COALESCE(p.Culture, s.Culture, d.Culture) Culture
FROM (SELECT DISTINCT Id FROM Project) x
LEFT OUTER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY Created DESC) Row, Id, Title, [Description], Culture
    FROM Project WHERE Culture = 'en-US') d ON d.Id = x.Id AND d.Row = 1 --DefaultCulture
LEFT OUTER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY Created DESC) Row, Id, Title, [Description], Culture
    FROM Project WHERE Culture = 'en-AU') p ON p.Id = x.Id AND p.Row = 1 --PrimaryCulture
LEFT OUTER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY Created DESC) Row, Id, Title, [Description], Culture
    FROM Project WHERE Culture = 'en-GB') s ON s.Id = x.Id AND s.Row = 1 --SecondaryCulture

Answer 1

something like this would work:

case 
    when coalesce(p.[Description], s.[Description]) is null then d.[Description]
    else coalesce(p.[Description], s.[Description])
end as [Description]

Edit: hmmm, a second reading of this answer reminds me never to attempt sql after a long day :P