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.