CODEWITHSUNDEEP
mysqlsql
Imran
Imran

Reputation: 1

How to merge tables in MySQL

Table 1

S.NO BOX
1 Basket1
2 Basket2
3 Basket3

Table 2

FRUIT BOX FRUITS
Basket1 Mango
Basket1 Apple
Basket1 Grapes
Basket1 Banana
Basket2 Banana
Basket2 Apple

Table3

VEGETABLES BOX VEGETABLES
Basket1 Tomato
Basket1 Potato
Basket2 Cucumber
Basket2 Potato
Basket3 Tomato

getting the output as

S.NO BOX FRUITS VEGETABLES
1 Basket1 Mango Tomato
1 Basket1 Apple Potato
1 Basket1 Grapes Tomato
1 Basket1 Banana Potato

if we check under vegetables column for basket1 it is repeating the same values again it should be NULL.

Thanks in advance!

Expecting the below output table

S.NO BOX FRUITS VEGETABLES
1 Basket1 Mango Tomato
1 Basket1 Apple Potato
1 Basket1 Grapes Null
1 Basket1 Banana Null

Upvotes: 1

Views: 102

Answers (1)

P.Salmon
P.Salmon

Reputation: 17615

Using row_number window function you can derive a row number to join on for example

with 
cte1 as
(select S_NO,FRUIT_BOX,ROW_NUMBER() OVER (PARTITION BY S_NO ORDER BY FRUITS) RN,
    FRUITS
 FROM T1
 LEFT JOIN T2 ON T2.FRUIT_BOX = T1.BOX
),
cte2 as
(select S_NO,VEGETABLES_BOX,ROW_NUMBER() OVER (PARTITION BY S_NO ORDER BY VEGETABLES) RN,
  VEGETABLES
 FROM T1
 LEFT JOIN T3 ON T3.VEGETABLES_BOX = T1.BOX
),
cte3 as
(SELECT s_no,RN FROM CTE1 union select s_no,rn from cte2)

select cte3.s_no,t1.box,
         cte1.fruits,
         cte2.vegetables 
from cte3
left join cte1 on cte3.s_no = cte1.s_no and cte3.rn = cte1.rn
left join cte2 on cte3.s_no = cte2.s_no and cte3.rn = cte2.rn
left join t1 on t1.s_no = cte3.s_no;

Upvotes: 1

Related Questions