Sql-server – Getting the name of the column that was used in a join

join;sql server

This may be a bit of a weird question:

Our project's datamodel has multiple tables with multiple columns any one of which can be used to join with one settingtable. The question here is how to get the name of the column that was used to connect to the settingtable in an OR join?

For instance, let's say the query looks like this:

SELECT FIRST.ID, SEC.ID, TRD.ID, FRTH.ID, SETS.ID, SETS.VALUE
FROM ROOTTABLE FIRST
LEFT JOIN SECONDTABLE SEC ON SEC.SECDONTABLE_ID = FIRST.SECONDTABLE_ID
LEFT JOIN THIRDTABLE TRD ON TRD.THIRDTABLE_ID = FIRST.THIRDTABLE_ID
LEFT JOIN FOURTHTABLE FRTH ON FRTH.FOURTHTABLE_ID = TRD.FOURTHTABLE_ID
-- etc...
LEFT JOIN SETTINGTABLE SETS 
   ON (SETS.ID = FIRST.ONESETTING_ID OR SETS.ID = FIRST.SECONDSETTING_ID
      OR SETS.ID = FIRST.THIRDSETTING_ID OR SETS.ID = TRD.RANDSETTING_ID
      OR SETS.ID = FRTH.ONESETTING_ID
-- etc...
   )

Now, that's a very parsed example of what's really going on. We may have dozens of tables with several joinin columns on each one to the SETTINGTABLE.

Obviously, merely making a separate join for each table.column would show you the column used. Or alternately, you can use CASE clause in the select. But either one is tricky and requires a lot of manual input. Even just using all the joining ID-columns in the select would enable you to compare the ID's, but since the results need to be readable by non-technical people, that wouldn't help much.

So is there a way to use some sys objects or somesuch to simply return the column name that was actually used to connect to SETTINGTABLE? The design is what it is, and will not change. So I'm simply trying to find the best way to work with what we have. This would significantly speed up our diagnosis of some of our scripts, queries and data.

Thanks!

Best Answer

There is no sys object or DMV that will give you such information. You'll have to build it yourself, for which you can have a look at the example below.

Let's prepare some tables and dummy data.

CREATE TABLE ROOTTABLE (FIRST_ID INT, SECOND_ID INT, THIRD_ID INT, ONESETTING_ID INT);
CREATE TABLE SECONDTABLE (SECOND_ID INT, SECONDSETTING_ID INT);
CREATE TABLE THIRDTABLE (THIRD_ID INT, FOURTH_ID INT, RANDSETTING_ID INT, THIRDSETTING_ID INT);
CREATE TABLE FOURTHTABLE (FOURTH_ID INT, ONESETTING_ID INT);
CREATE TABLE SETTINGTABLE (ID INT, VALUE Varchar(10));

INSERT ROOTTABLE VALUES (1,2,3,15),(1,1,1,12),(11,5,6,12);
INSERT SECONDTABLE VALUES (1,11),(2,15),(5,13);
INSERT THIRDTABLE VALUES (1,5,6,15),(6,7,8,15),(3,7,8,15);
INSERT FOURTHTABLE VALUES (5,15),(6,16),(7,17),(1,18);
INSERT SETTINGTABLE VALUES
  (11,'one'),
  (12,'two'),
  (13,'three'),
  (14,'four'),
  (15,'five'),
  (16,'six'),
  (17,'seven');

Here's the query with the MATCHES column to show where the JOIN condition was fulfilled from (SETS.ID = ?).

SELECT FIRST.FIRST_ID, SEC.SECOND_ID, TRD.THIRD_ID, FRTH.FOURTH_ID, SETS.ID, SETS.VALUE,
       STUFF((SELECT ','+SOURCE_TABLE
              FROM (VALUES
                    ('ROOTTABLE',   FIRST.ONESETTING_ID),  -- 1
                    ('SECONDTABLE', SEC.SECONDSETTING_ID), -- 2
                    ('THIRDTABLE',  TRD.THIRDSETTING_ID),  -- 3
                    ('THIRDTABLE',  TRD.RANDSETTING_ID),   -- 4
                    ('FOURTHTABLE', FRTH.ONESETTING_ID))   -- 5
                   X (SOURCE_TABLE, SOURCE_ID)
              WHERE X.SOURCE_ID = SETS.ID
              FOR XML PATH('')),1,1,'') MATCHES
FROM ROOTTABLE FIRST
LEFT JOIN SECONDTABLE SEC ON SEC.SECOND_ID = FIRST.SECOND_ID
LEFT JOIN THIRDTABLE TRD ON TRD.THIRD_ID = FIRST.THIRD_ID
LEFT JOIN FOURTHTABLE FRTH ON FRTH.FOURTH_ID = TRD.FOURTH_ID
LEFT JOIN SETTINGTABLE SETS 
     ON (SETS.ID = FIRST.ONESETTING_ID    -- 1
      OR SETS.ID = SEC.SECONDSETTING_ID   -- 2
      OR SETS.ID = TRD.THIRDSETTING_ID    -- 3
      OR SETS.ID = TRD.RANDSETTING_ID     -- 4
      OR SETS.ID = FRTH.ONESETTING_ID);   -- 5

Note that I have laid out the sources that could make the match (1-5) in both the VALUES rows constructor and ON clauses so that you can follow the pattern.

Results:

| FIRST_ID | SECOND_ID | THIRD_ID | FOURTH_ID | ID | VALUE |                          MATCHES |
-----------------------------------------------------------------------------------------------
|        1 |         2 |        3 |         7 | 15 |  five | ROOTTABLE,SECONDTABLE,THIRDTABLE |
|        1 |         2 |        3 |         7 | 17 | seven |                      FOURTHTABLE |
|        1 |         1 |        1 |         5 | 11 |   one |                      SECONDTABLE |
|        1 |         1 |        1 |         5 | 12 |   two |                        ROOTTABLE |
|        1 |         1 |        1 |         5 | 15 |  five |           THIRDTABLE,FOURTHTABLE |
|       11 |         5 |        6 |         7 | 12 |   two |                        ROOTTABLE |
|       11 |         5 |        6 |         7 | 13 | three |                      SECONDTABLE |
|       11 |         5 |        6 |         7 | 15 |  five |                       THIRDTABLE |
|       11 |         5 |        6 |         7 | 17 | seven |                      FOURTHTABLE |

Some things to note:

  1. The multiple ORs in the JOIN mean that SETS.ID could be matched by any of the sources - the query shows them all comma separated.
  2. Rows 3-5 with the leading columns (1, 1, 1, 5) match SETS.ID three times.

SQL Fiddle

For clarity, this other SQL Fiddle shows the additional *SETTING_ID columns that are used to match against SETS.ID.

Related Question