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?