table1 | id | value
--------------------
1 | john
2 | frank
3 | patel
4 | jim
table2 | id | value
--------------------
6 | steve
7 | tim
8 | sunny
9 | bhaskar
merged | tabid1 | tabid2
------------------------------
3 | 7
4 | 8
output needed:
output | tabid1 | tabval1 | tabid2 | tabval2
1 | john | NULL | NULL
2 | frank | NULL | NULL
3 | patel | 7 | tim
4 | jim | 8 | sunny
NULL | NULL | 6 | steve
NULL | NULL | 9 | bhaskar
I tried:
SELECT *
FROM table1
LEFT JOIN merged m1 ON table1.id = m1.tabid1, merged m2
RIGHT JOIN table2 ON table2.id = m2.tabid2
But its giving 16 rows instead of required 6
select table1.id,table1.value,table2.id,table2.value
from table1 left join merged on table1.id=merged.tabid1
left join table2 on merged.tabid2=table2.id
union
select table1.id,table1.value,table2.id,table2.value
from table2 left join merged on table2.id=merged.tabid2
left join table1 on merged.tabid1=table1.id ;
or with right join:
SELECT * FROM
table1 LEFT JOIN merged on table1.id=merged.tabid1
LEFT JOIN table2 ON merged.tabid2=table2.id
UNION
SELECT * FROM
table1 RIGHT JOIN merged on table1.id=merged.tabid1
RIGHT JOIN table2 ON merged.tabid2=table2.id WHERE table1.id IS NULL;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With