Using a Selected table for two sets of joins-CodePudding

I'm rationalising some old SQL tables that exist at a lot of remote sites, so I need to build a query that will make new good tables out of the bad old ones. So for this, we have table1 which has the columns DataGroup and Case1 as nvarchar, but these are enums in the application, so I've made new tables to store the enums, but I need to get the IDs. Unfortunately, we need to store all of the enums for this table in a single table, so the ExData table contains 4 columns: id, name, ExGroupId and DataGroupId.

As DataGroup in table1 is text, we need to look that up for the int id as well from a kvp table DataGroupTable

This is the query I have so far:

SELECT
    <Other Columns>,
    t1.ExDataId AS Case1
FROM
    table1
    LEFT JOIN (
        SELECT
            DataGroupTable.name AS dataGroup,
            ExData.id AS ExDataId,
            ExData.name AS ExDataName,
            ExGroup.name AS ExGroupName
        FROM
            ExData
            LEFT JOIN DataGroupTable ON DataGroupTable.id = ExData.dataGroupId
            LEFT JOIN ExGroup ON ExGroup.id = ExData.ExGroupId
    ) t1 ON t1.dataGroup = table1.DataGroup
    AND t1.ExGroupName = 'case1'
    AND t1.ExDataName = table1.Case1
GO

... But while this works to retrieve Case1, how would I go about getting Case2? I have 7 cases to handle, and whilst I could solve this with liberal copy-pasting, that is far from elegant.

Additionally, this is all going into an INSERT statment, so ideally this should return Case1, Case2 etc as ExDataId's

Please help.

Sample Data as requested, All id's will start from 0, but I have made all of the below unique for clarity.

table1:

DataGroup   Case1   Case2   Case3   <Other Columns>
ABCD        bob     bob    chris   1
ABCD        pete    gary    chris   2
EFGH        bob     mike    rod     3

DataGroupTable:

id   name
11    ABCD
12    EFGH

ExGroup:

id   name
21    case1
22    case2
23    case3

ExData:

id   name   ExGroupId   dataGroupId
31    bob    21          11
32    pete   21          11
33    bob    21          12
34    bob    22          11
35    gary   22          11
36    mike   22          12
37    chris  23          11
38    rod    23          12

Ideal Result:

<Other Columns>   Case1   Case2   Case3
1                 31      34      37
2                 32      35      38
3                 33      36      38

CodePudding user response：

How about a Common Table Expression ?

WITH ExDataCTE AS (
    SELECT
        DataGroupTable.name AS dataGroup,
        ExData.id AS ExDataId,
        ExData.name AS ExDataName,
        ExGroup.name AS ExGroupName
    FROM
        ExData
        LEFT JOIN DataGroupTable ON DataGroupTable.id = ExData.dataGroupId
        LEFT JOIN ExGroup ON ExGroup.id = ExData.ExGroupId)
SELECT
    <Other Columns>,
    t1.ExDataId AS Case1,
    t2.ExDataId AS Case2,
    t3.ExDataId AS Case3
FROM
    table1
    LEFT JOIN ExDataCTE t1 ON (t1.dataGroup = table1.DataGroup 
                           AND t1.ExGroupName = 'case1'
                           AND t1.ExDataName = table1.Case1)
    LEFT JOIN ExDataCTE t2 ON (t2.dataGroup = table1.DataGroup 
                           AND t2.ExGroupName = 'case2'
                           AND t2.ExDataName = table1.Case2)
    LEFT JOIN ExDataCTE t3 ON (t3.dataGroup = table1.DataGroup 
                           AND t3.ExGroupName = 'case3'
                           AND t3.ExDataName = table1.Case3)