SQL Query: How to Join on Different Tables Based on Column Value

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.

You can use ADDTIME() function:

tableA AS a JOIN tableB AS b
  ON a.datetime_column = ADDTIME(CAST(b.date_column AS DATETIME), b.time_column)

This might use an index on tableA (datetime_column) but not an index on tableB. The reverse might use an index on tableB (date_column, time_column) but not on A:

tableA AS a JOIN tableB AS b
  ON  CAST(a.datetime_column AS DATE) = b.date_column
  AND CAST(a.datetime_column AS TIME) = b.time_column

It won't hurt testing both versions. If one table is much larger than the other, then prefer to have the larger table's columns exposed (not cast) so their index might be used.

If you move to MariaDB (any version 5.3+) or MySQL 5.7 (when it's released), you can define a VIRTUAL column (or two) in one of the two tables to hold this conversion/calculation that can be persisted and indexed.

In 5.5, if efficiency is not good, which is expected with large tables, you could add a computed column yourself but it would have to be populated during inserts and kept in sync during updates by you (e.g. using triggers).