Reputation: 501
The title might not be the most accurate one, but I have the following diagram:
Notice that the CultureTranslations table has a combined PK
from 3 other FK
s and an NVARCHAR(256)
column (ResourceKey).
The CultureTranslations table holds data similar to:
ApplicationId | CultureCodeId | ResourceGroupId | ResourceKey | ResourceValue |
-------------------------------------------------------------------------------
1 | 1 | 1 | X | X (US)
1 | 1 | 1 | Y | Y (US)
1 | 1 | 1 | Z | Z (US)
....
1 | 2 | 1 | X | X (GB)
1 | 2 | 1 | Z | Z (GB)
and so on...
where CultureCodeId 1 = en-US
, 2 = en-GB
.
The thing is that Culture en-GB
might not have records for all equivalent en-US
records, only for some.
How can I write a query or a view which, based on the above, will combine resources for different cultures in the same row/record? E.g.:
s.ApplicationId | s.ResourceGroupId | s.ResourceKey | s.ResourceValue | d.ResouceValue |
----------------------------------------------------------------------------------------
1 | 1 | X | X (US) | X (GB)
1 | 1 | Y | Y (US) | null
1 | 1 | Z | Z (US) | Z (GB)
where s = source culture (en-US
), d = destination culture (en-GB
).
It should always show all ResourceKeys from the source but put null
in the d.ResourceValue
column where no resource has been found for that particular ResourceKey, ApplicationId & ResourceGroupId.
Upvotes: 1
Views: 57
Reputation: 38073
Using a left join
and putting part of your where
clause in the join specification
will get you the results you are looking for.
test setup: http://rextester.com/UXUZXH20794
create table CultureTranslation (
ApplicationId int not null
, CultureCodeId int not null
, ResourceGroupId int not null
, ResourceKey nvarchar(256)
, ResourceValue nvarchar(256)
);
insert into CultureTranslation values
(1,1,1,'X','X,(US)')
,(1,1,1,'Y','Y,(US)')
,(1,1,1,'Z','Z,(US)')
,(1,2,1,'X','X,(GB)')
,(1,2,1,'Z','Z,(GB)');
query:
select
s.ApplicationId
, s.ResourceGroupId
, s.ResourceKey
, s.ResourceValue
, d.ResourceValue
from
CultureTranslation s
left join CultureTranslation d
on s.ApplicationId = d.ApplicationId
and s.ResourceGroupId = d.ResourceGroupId
and s.ResourceKey = d.ResourceKey
and d.CultureCodeId = 2
where s.CultureCodeId = 1
results:
+---------------+-----------------+-------------+---------------+---------------+
| ApplicationId | ResourceGroupId | ResourceKey | ResourceValue | ResourceValue |
+---------------+-----------------+-------------+---------------+---------------+
| 1 | 1 | X | X,(US) | X,(GB) |
| 1 | 1 | Y | Y,(US) | NULL |
| 1 | 1 | Z | Z,(US) | Z,(GB) |
+---------------+-----------------+-------------+---------------+---------------+
Upvotes: 1
Reputation: 674
select A.,B. from CultureCode A inner join CultureCode B on A.ResourceKey=B.ResourceKey where A.CultureCodeId=B.CultureCodeId
Upvotes: 0