Query: How to join on different tables depending on the filled column?

Question

I'm pretty new to the field of SQL, but already solved a lot of problems, so until now i am familiar with the different possibilites of JOIN(s) (explicit and implicit syntax), group by, distinct and so on.

But the query i am working on right now is crushing my head and i'm not sure how to approach that one:

The data i got is about roads and junctions, one road segment is defined from one junction up until the next one.

Segment Table:

| ID | start_junc | end_junc | ... |
| 1  | 1518       | 3645     | ... |
| 2  | 3645       | 769      | ... |

But there are different types of junctions, so the start_junc and end_junc FK are for a table looking like this:

| ID   | type        | junc_type_A | junc_type_B | junc_type_C |
| 769  | junc_type_C | <NULL>      | <NULL>      | 63          |
| 1518 | junc_type_B | <NULL>      | 17          | <NULL>      |
| 3645 | junc_type_A | 42          | <NULL>      | <NULL>      |
| ...  | junc_type_B | <NULL>      | 16          | <NULL>      |
| ...  | junc_type_B | <NULL>      | 13          | <NULL>      |

So for every ID there is exactly one junction type, all other junction types are NULL value and additional the the type of it is written in the column 'type'. Finally there is a table for each junction type with the ID and attributes for the junction like name,...

junc_type_A
| ID | name | ... |
| 42 | abc  | ... |
| 43 | def  | ... |
| .. | ...  | ... |

junc_type_B
| ID | name | ... |
| 13 | jkl  | ... |
| 16 | mno  | ... |
| 17 | pqr  | ... |

and of course for junc_type_C which i will omit.

What is needed is a query that additional outputs the start and end junctions name to the segments table:

| ID | start_junc | end_junc | ... | start_junc_name | end_junc_name |
| 1  | 1518       | 3645     | ... | pqr             | abc           |

So, doing it manually it seems like an easy piece... from the start_junc ID join the next table on that ID, look which column is not null, join that table with the name of the column... But i have no idea how to approach this in SQL. Hope it is possible without writing a procedure.

And one additional question: How can this kind of relation (for the junction type table where there is a column for every type of junction table and all other values are NULL) be called, so that i have an idea how to search for that in the future?

Answer 1

Let's say that your tables are called (in order of you mentioning them):

1. Segment,
2. FK,
3. JC_type_A,
4. JC_type_B,
5. etc

The main problem that I can see is that you don't know which junc_type will be for which junc. So you will have to left join back to their respective tables and select the one that is not null in your select statement.

Something along the lines of the following should help to be a template for you.

SELECT  s.Id,
        s.start_junc,
        s.end_junc,
        -- Coalesce works here as there are more than 2 potential NULL values
        --  and only 1 of them will be NOT NULL as you have said.
        start_junc_name = COALESCE(ja_start.name, jb_start.name, jc_start.name),
        end_junc_name   = COALESCE(ja_end.name, jb_end.name, jc_end.name),
FROM    #Segment AS s
JOIN    #FK AS f_start
ON      s.start_junc = f_start.Id
JOIN    #FK AS f_end
ON      s.end_junc = f_end.Id
-- get junc_a start if exists, else return null...
LEFT 
JOIN    #JC_type_A AS ja_start
ON      f_start.junc_type_A = ja_start.Id
-- get junc_a end if exists, else return null...
LEFT 
JOIN    #JC_type_a AS ja_end
ON      f_end.junc_type_A = ja_end.Id
--     [...continue for rest of tables...]