T-SQL Where statement - OR

Question

Sorry for the vague title but I really didn't know what title to give to this problem:

I have the following result set:

ID   Field   FieldValue   Culture
1    Color   Groen        nl-NL
2    Taste   Kip          nl-NL
3    Color   Green        en-GB
4    Taste   Chicken      en-GB
5    Color   Green        en
6    Taste   Chicken      en

I would like to mimic the ASP.Net way of resource selection, user Culture (nl-NL)

SELECT Field, FieldValue FROM Tbl WHERE Culture = 'nl-NL'

Or when there are no results for the specific culture, try the parent Culture (nl)

SELECT Field, FieldValue FROM Tbl WHERE Culture = 'nl'

Or when there are no results for the parent culture, try the default Culture (en)

SELECT Field, FieldValue FROM Tbl WHERE Culture = 'en'

How can I combine these SELECT-statements in one T-SQL statement?
I'm using LINQ so a LINQ expression would be even greater.

The OR-statement won't work, because I don't want a mix of cultures.
The ORDER BY-statement won't help, because it returns multiple records per culture.

The output might look like (nl-NL):

Color   Groen
Taste   Kip

or (en-GB / en):

Color   Green
Taste   Chicken

Answer 1

So, here is the code that does exactly what's written in your example. It is expected that you determine specific culture (nl-NL), neutral culture (nl), and fallback culture (en) in the client-side code and feed these values to SQL.

SELECT Field, FieldValue FROM Tbl WHERE Culture = (
    SELECT TOP 1 Culture FROM (
        SELECT 1 as n, Culture FROM Tbl WHERE Culture = 'nl-NL'
        UNION
        SELECT 2, Culture FROM Tbl WHERE Culture = 'nl'
        UNION
        SELECT 3, Culture FROM Tbl WHERE Culture = 'en'
    ) cultures
    ORDER BY n
)

Are you sure you need the whole translation table for a given culture, not a translation of each string? What if you have string A and B in en, but only string A in nl-NL?

Answer 2

Assuming Culture is being passed in in @Culture:

select Field,COALESCE(fc.FieldValue,pc.FieldValue,dc.FieldValue)
from
    Tbl dc
        left join
    Tbl pc
        on
            dc.Field = pc.Field and
            pc.Culture = SUBSTRING(@Culture,1,CHARINDEX('-',@Culture)-1)
        left join
    Tbl fc
        on
            dc.Field = fc.Field and
            fc.Culture = @Culture
where
    dc.Culture = 'en'

will return the entire table (the aliases could be longer - dc = default culture, pc = partial culture, fc = full culture.

Answer 3

Try this:

SELECT Field, FieldValue FROM Tbl WHERE Culture = 'nl-NL' 

union

SELECT Field, FieldValue FROM Tbl WHERE Culture = 'nl' 
and not exists(select * from tbl where culture = 'nl-NL')

union

SELECT Field, FieldValue FROM Tbl WHERE Culture = 'en' 
and not exists(select * from tbl where culture = 'nl-NL')
and not exists(select * from tbl where culture = 'nl')

Linq:

var x = (from a in tbl
        where culture == "nl-NL"
        select a
        )

        .Union

        (from a in tbl
        where culture == "nl"
        && !tbl.Any(c => c.Culture == "nl-NL")
        select a
        )

        .Union
        (from a in tbl
        where culture == "en"
        && !tbl.Any(c => c.Culture == "nl-NL")
        && !tbl.Any(c => c.Culture == "nl")
        select a
        );

Answer 4

Given a target culture return either the complete match, or failing that a match on the parent. (Assumed to be the left 2 characters from the Culture field)

(UPDATED)

-- @TargetCulture represents the culture we are interested in
DECLARE @TargetCulture varchar(5)
SET @TargetCulture = 'nl-NL'

-- Parent represents the Result_Set (as in OP) plus column for the 'parent' culture
;WITH Parent AS
(
    SELECT 
        *, 
        LEFT(Culture, 2) AS parent
    FROM 
        Result_Set
)
SELECT 
    * 
FROM 
    Parent
WHERE
    -- Get either a full matching culture or just a match on the parent
    (@TargetCulture = Culture AND @TargetCulture <> parent)
    OR
    (@TargetCulture <> Culture AND @TargetCulture = parent)

Change the value of the @TargetCulture variable to retrieve culture data for nl, en-GB, and en.

Answer 5

try this:

SELECT Field, FieldValue FROM Tbl WHERE Culture = 
coalesce
(
(select top 1 culture from tbl where culture = 'nl-NL'),
(select top 1 culture from tbl where culture = 'nl'),
(select top 1 culture from tbl where culture = 'en')
)

Linq-fu:

var v = 
    from a in Tbl
    where a.Culture ==
        (

            (from c in Tbl
            where c.Culture == "nl-NL"
            select c.Culture).FirstOrDefault()

            ??
            (from c in Tbl
            where c.Culture == "nl"
            select c.Culture).FirstOrDefault()

            ??
            (from c in Tbl
            where c.Culture == "en"
            select c.Culture).FirstOrDefault()
        )
    select new { a.Field, a.FieldValue };

Answer 6

I might be missing something but it seems to me you can do something like

SELECT * FROM Tablename
WHERE
(LEN(@Culture) = 5 AND Culture = @Culture) OR
(LEN(@Culture) = 2 AND LEFT(Culture,2) = @Culture) OR
@Culture='' AND Culture='en'

This seems a lot simpler than all the other solutions so I may have missed something as it has been a very long day.

Answer 7

Another method;

SELECT * FROM tbl WHERE tbl.Culture = (
 SELECT COALESCE(
  CASE WHEN EXISTS (SELECT 1 FROM tbl WHERE Culture = @locale) THEN @locale ELSE NULL END,
  CASE WHEN EXISTS (SELECT 1 FROM tbl WHERE Culture = SUBSTRING(@locale, 1, CHARINDEX('-', @locale + '-') - 1)) THEN SUBSTRING(@locale, 1, CHARINDEX('-', @locale + '-') - 1) ELSE NULL END,
  'en'
 )
)


nl-nl -> 'nl-nl'
en -> 'en'
en-xxx -> 'en'
zz-gg -> 'en'
en-gb -> 'en-gb'
ff -> 'en'