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:
- The multiple
OR
s in the JOIN mean that SETS.ID
could be matched by any of the sources - the query shows them all comma separated.
- 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.
Best Answer
I would just join the tables directly
Coalesce is fine here I think
You could put the 3 tables in a view or cte