This is my table :

I want the ouput like :

I have tried this :

SELECT CONCAT( CASE WHEN `Report_TO` = `PKEY` THEN Name ELSE CONCAT( "no one " ) END )

Now i get output as :

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