CODEWITHSUNDEEP
mysqlunion
Margot
Margot

Reputation: 45

Union mysql tables with just one common column

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

Answers (1)

computercarguy
computercarguy

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

Related Questions