Hi This may be a stupid question but I have a scenario where my columns tl1,tl2 and tl3 refers to column id table structure is below,
id | name | email | tl1 | tl1 | tl3 |
1 | xyz |[email protected]| null| 1 | 2 |
======================================
2 | abc |[email protected]| null| 1 | 3 |
======================================
3 | def |[email protected]| 1 | 2 | 4 |
SO need to write a query to get the name of tl1,tl2 and tl3,
like below
id | name | email | tl1 | tl1 | tl3 |
1 | xyz |[email protected]| null| xyz | abc |
======================================
2 | abc |[email protected]| null| xyz | def|
======================================
3 | def |[email protected]| xyz | abc | ghi |
I am not able to create query for this scenario, I actually created one but not getting unique records
This is the query which I tried
select distinct a.branch_code,a.email,a.partner_name,a.role_assigned,a.category,b.partner_name as tl1,
c.partner_name as tl2,d.partner_name as tl3
from branch_master as a
left join branch_master as b on b.tl1 = a.branch_code
left join branch_master as c on c.tl2 = a.branch_code
left join branch_master as d on d.tl3 = a.branch_code
Any help would be appreciated.
You need to left join the table 3 times with itself:
select
t.id, t.name, t.email,
t1.name name1, t2.name name2, t3.name name3
from tablename t
left join tablename t1 on t1.id = t.tl1
left join tablename t2 on t2.id = t.tl2
left join tablename t2 on t3.id = t.tl3