Join fom two source tables into a single column

How would I accomplish this?

I’m lost on how to accomplish this. I have a table with relationships to a like named field in two different tables. I’m trying to join them such that the two columns get merged into a single one. The behavior I’m looking for is as follows

1. all structure Ids and description pairs present in the structure table are represented in the final product. Where they do not have corresponding unit_ids there should be nulls (as in structure_id 7)
2. Each structure_id/unit_id pair should be represented in the final table.
3. where the two tables have different unit_ids for the same structure_ids all pairs should be represented (as in as in structure_id 3)
4. where the two tables have the same pairs, each should only be represented once (as in structure_id 6)

Any help figuring out the precise join/subquery requirements to produce this result would be immensely appreciated, it’s been a long time since I’ve done this.

Answer

A way to achieve this is to dynamically alter that data model by combining the multiple unit tables into one, e.g.:

select
     structure.structure_id, unit_tab.unit_id, structure.description
from structure
left join (select structure_id, unit_id from unit_tab_1
            union
            select structure_id, unit_id from unit_tab_2
           ) AS unit_tab on structure.structure_id = unit_tab.structure_id

Perhaps you haven’t revealed some detail but from what I can see there isn’t a good reason to have different unit tables. If you cannot combine them consider creating a view that achieves a similar outcome to the union subquery above. I have used union in the subquery to remove duplicate structure_id/unit_id pairs.