Skip to content
Advertisement

Is there a best way to join multiple tables

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.

enter image description here

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.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement