Reputation: 45
Please help me to find a solution for my case. I have n tables with follow structure
main_id | sec_id | col_name_1 |
---|---|---|
1 | 9 | aaa |
2 | 8 | bbb |
main_id | sec_id | col_name_2 |
---|---|---|
1 | 9 | ccc |
2 | 8 | bbb |
...
main_id | sec_id | col_name_n |
---|---|---|
1 | 9 | ddd |
3 | 7 | eee |
What I want to have is:
main_id | sec_id | col_name1 | col_name_2 | ... | col_name_n |
---|---|---|---|---|---|
1 | 9 | aaa | ccc | ddd | |
2 | 8 | bbb | bbb | null | |
3 | 7 | null | null | eee |
Is it possible to union such tables?
Upvotes: 0
Views: 212
Reputation: 2473
That's not a union
, it's a join
.
Select table1.main_id, table1.sec_id, col_name1, col_name2, ..., col_name_n
From table1
Cross Join table2 on table1.main_id = table2.main_id
Cross Join table3 on table1.main_id = table3.main_id
...
Cross Join tableN on table1.main_id = tableN.main_id
Where ...
The problem you are going to run into is getting the "main_id" and "sec_id" for "table3" if they don't exist in "table1". You can use a null coalesce, but that gets really ugly when looking at as many tables you describe.
Select COALESCE(table1.main_id, table2.main_id, table3.main_id, ..., tableN.main_id),
COALESCE(table1.sec_id, table2.sec_id, table3.sec_id, ..., tableN.sec_id),
...
Learn more about joins and unions to figure out what's appropriate for you needs.
A join
is when you want to intermix your columns and when they have one or multiple common column(s) that you can match rows to each other, like you are trying to do.
A union
is where you are doing different queries on different (or the same) tables, but you want a single list, which also tends to mean that the columns are the same or renamed to be the same.
Upvotes: 2