T-SQL Where Statement - OR
Sorry for the vague title, but I really didn't know what title to give 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 emulate the ASP.Net resource selection method, user Culture (nl-NL)
SELECT Field, FieldValue FROM Tbl WHERE Culture = 'nl-NL'
Or when there are no culture-specific results, try the parent culture (nl)
SELECT Field, FieldValue FROM Tbl WHERE Culture = 'nl'
Or if there are no results for the parent culture, try using the default Culture (en)
SELECT Field, FieldValue FROM Tbl WHERE Culture = 'en'
How can I combine these SELECT statements in a single T-SQL statement?
I am using LINQ, so the LINQ expression will be even bigger.
The OR operation won't work because I don't want a mix of cultures.
The ORDER BY clause won't help because it returns multiple records per culture.
The result might look like (nl-NL):
Color Groen
Taste Kip
or (ru-RU / ru):
Color Green
Taste Chicken
a source to share
So here's the code that does exactly what your example says. You are expected to define culture-specific (nl-NL), culture-neutral (nl), and fallback culture (en) in your client code and load these values into 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 want the entire translation table for a given culture, not a translation of each line? What if you have string A and B in en, but only string A in nl-NL?
a source to share
Try the following:
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
);
a source to share
Assuming culture is being passed to @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 (aliases can be longer - dc = default culture, pc = partial culture, fc = full culture.
a source to share
Given that the target culture returns either a complete match or a non-parent match. (This assumes these are 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 variable @TargetCulture
to get culture data for nl
, en-GB
and en
.
a source to share
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'
a source to share