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?
>Solution :
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
Join table2 on table1.main_id = table2.main_id
Where ...
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.