Replace column value from joined table if column name matches

Mehmet Ince 0
Viewed 47

I have two tables

TABLE_A:

ID NAME DESCRIPTION
1 Main First Firstly Original
2 Main Second Secondly Original
3 Main Third Thirdly Original

RESOURCES:

LANG_ID CONTEXT_ID CONTEXT KEY VALUE
1 1 TABLE_A NAME Frst Lang 1
2 1 TABLE_A NAME Frst Lang 2
2 2 TABLE_A DESCRIPTION Scnd Lang 2

My expected results:

ID NAME DESCRIPTION LANG_ID
1 Frst Lang 1 Firstly Original 1
2 Frst Lang 2 Scnd Lang 2 2
3 Main Third Thirdly Original NULL

What I have tried so far is:

SELECT        REF.ID, 
              CASE WHEN RES.KEY = 'NAME' THEN RES.VALUE  ELSE REF.NAME END AS NAME,
              CASE WHEN RES.KEY = 'DESCRIPTION' THEN RES.VALUE  ELSE REF.DESCRIPTION END AS DESCRIPTION,
              RES.LANG_ID
         FROM (
                   SELECT * FROM TABLE_A
              ) REF
    LEFT JOIN (
                    SELECT * FROM RESOURCES
                     WHERE CONTEXT = 'TABLE_A' 
              ) RES
            ON REF.ID = RES.CONTEXT_ID

But it didn't produce the expected result. How can I succeed this by SQL?

join sql-server sql
1 Answers

Your question could do with some further clarification specifically that TABLE_A.ID = RESOURCES.LANG_ID.

Assuming that is correct you can obtain the results you want using OUTER APPLY to see if there is a replacement row available from your RESOURCE table.

declare @TableA table (ID int, [NAME] varchar(32), [DESCRIPTION] varchar(32));

insert into @TableA (ID, [NAME], [DESCRIPTION])
values
(1, 'Main First', 'Firstly Original'),
(2, 'Main Second', 'Secondly Original'),
(3, 'Main Third', 'Thirdly Original');

declare @Resources table (LANG_ID int, CONTEXT_ID int, CONTEXT varchar(32), [KEY] varchar(32), [VALUE] varchar(32));

insert into @Resources (LANG_ID, CONTEXT_ID, CONTEXT, [KEY], [VALUE])
values
(1, 1, 'TABLE_A', 'NAME', 'Frst Lang 1'),
(2, 1, 'TABLE_A', 'NAME', 'Frst Lang 2'),
(2, 2, 'TABLE_A', 'DESCRIPTION', 'Scnd Lang 2');

select A.ID
    , case when N.LANG_ID is not null then N.[VALUE] else [NAME] end [NAME]
    , case when D.LANG_ID is not null then D.[VALUE] else [DESCRIPTION] end [DESCRIPTION]
    , case when N.LANG_ID is not null or D.LANG_ID is not null then A.ID else null end LANG_ID
from @TableA A
outer apply (
  select LANG_ID, [VALUE]
  from @Resources R
  where R.CONTEXT = 'TABLE_A' and R.[KEY] = 'NAME' and R.LANG_ID = A.ID
) as N (LANG_ID, [VALUE])
outer apply (
  select LANG_ID, [VALUE]
  from @Resources R
  where R.CONTEXT = 'TABLE_A' and R.[KEY] = 'DESCRIPTION' and R.LANG_ID = A.ID
) as D (LANG_ID, [VALUE]);

Returns:

ID NAME DESCRIPTION LANG_ID
1 Frst Lang 1 Firstly Original 1
2 Frst Lang 2 Scnd Lang 2 2
3 Main Third Thirdly Original NULL

Note: Providing sample data as DDL+DML (as shown here) makes it much easier for people to assist.

Dale K0
Related