Can some one please help in joining/merging the table like below.
I know how to do if the department columns (depart_1, depart_2, depart_3) are in one table. but not able to achieve this scenario as they are in different tables.
I have almost 100 fields like department, so little concern about performance as well.
Advertisement
Answer
By using JOIN
and UNION
SELECT id, name gender, 1 as seq, depart_1 as department FROM tab 1 UNION SELECT id, name gender, 2 as seq, depart_2 as department FROM tab 1 UNION SELECt tab1.id, tab2.name, tab1.gender, 3 as seq, tab2.depart_3 as department FROM tab2 JOIN tab1 on tab2.id = tab1.id UNION SELECt tab1.id, tab2.name, tab1.gender, 4 as seq, tab2.depart_4 as department FROM tab2 JOIN tab1 on tab2.id = tab1.id UNION SELECT tab1.id, tab3.name, tab1.gender, 5 as seq, tab3.depart_5 as department FROM tab3 JOIN tab1 on tab3.id = tab1.id UNION SELECT tab1.id, tab3.name, tab1.gender, 6 as seq, tab3.depart_6 as department FROM tab3 JOIN tab1 on tab3.id = tab1.id
Each query reads one department info. So you can us static number in each query for seq column.