CODEWITHSUNDEEP
sqlsql-serverdatabase
Teslo.
Teslo.

Reputation: 501

Combine data from same table into same records

The title might not be the most accurate one, but I have the following diagram:

enter image description here

Notice that the CultureTranslations table has a combined PK from 3 other FKs 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

Answers (2)

SqlZim
SqlZim

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

Singh Kailash
Singh Kailash

Reputation: 674

select A.,B. from CultureCode A inner join CultureCode B on A.ResourceKey=B.ResourceKey where A.CultureCodeId=B.CultureCodeId

Upvotes: 0

Related Questions