Search code examples
mysqlconcatenationstring-concatenation

MySQL syntax for CONCAT depending same table column?

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.

Solution

  • 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