This is my table :
pkey | Name | Report_to |
---|---|---|
11 | abc | 12 |
12 | def | 13 |
13 | sdf | 11 |
14 | dfg |
I want the ouput like :
Name | Report_to |
---|---|
abc | def |
def | sdf |
sdf | abc |
dfg |
I have tried this :
SELECT CONCAT( CASE WHEN `Report_TO` = `PKEY` THEN Name ELSE CONCAT( "no one " ) END )
Now i get output as :
Name | Report_to |
---|---|
abc | no one |
def | no one |
sdf | no one |
dfg | no one |
Is it possible to get the output as i wished.
CodePudding user response:
You can join multiple copies of the same table to itself, but in order to distinguish each such copy within your query all (but at most one) must be given some other, unique, alias by which to be known within the query.
Because you still want to include dfg
in the output even though there is no-one to whom they report, you require an outer join. There are two types of outer join, left and right, which indicate from which side of the join records should still be included even if the join predicate is not matched.
Therefore, you can use either (sqlfiddle):
SELECT reporter.Name AS Reporter, reportee.Name AS Reportee
FROM my_table AS reporter
LEFT JOIN my_table AS reportee
ON reportee.pkey = reporter.Report_to
ORDER BY reporter.pkey
or (sqlfiddle)
SELECT reporter.Name AS Reporter, reportee.Name AS Reportee
FROM my_table AS reportee
RIGHT JOIN my_table AS reporter
ON reportee.pkey = reporter.Report_to
ORDER BY reporter.pkey
Jeff Atwood (who co-founded Stack Overflow) wrote an excellent blog post that may be of interest to you, A Visual Explanation of SQL Joins.
CodePudding user response:
Simply join table to itself and use left join to select desired columns:
select tbl1.name , tbl2.name from docs tbl1
left join docs tbl2 on tbl1.report_to = tbl2.pkey
Note: replace table_name with your tables actual name :
table_name
--> your tables name
sql fiddle here