How do I get data from another column and row if certain criteria are met?-CodePudding

I have three columns Name, ClientId and GroupID

╔══════╦══════════╦═════════╗
║ Name ║ ClientId ║ GroupId ║
╠══════╬══════════╬═════════╣
║  abc ║        1 ║       1 ║
║  xyz ║        2 ║       2 ║
║  lmn ║        3 ║       3 ║
║  opq ║       50 ║       1 ║
║  def ║      543 ║       2 ║
║  rst ║      115 ║       0 ║
║  uvw ║        5 ║       5 ║
╚══════╩══════════╩═════════╝

I want to create one where :

if ClientId and GroupId are the same it displays Name as normal
if ClientId and GroupId are not the same it displays Name of the row in which it is
if GroupId = 0 then display the name as normal

╔══════╦══════════╦═════════╦══════════╗
║ Name ║ ClientId ║ GroupId ║ Fix_Name ║
╠══════╬══════════╬═════════╣══════════╣
║  abc ║        1 ║       1 ║    abc   ║
║  xyz ║        2 ║       2 ║    xyz   ║
║  lmn ║        3 ║       3 ║    lmn   ║
║  opq ║       50 ║       1 ║    abc   ║
║  def ║      543 ║       2 ║    xyz   ║
║  rst ║      115 ║       0 ║    rst   ║
║  uvw ║        5 ║       5 ║    uvw   ║
╚══════╩══════════╩═════════╝══════════╝

I've tried a few times using left join and union but in all cases it never displays the right name and always comes back as 0, in the case below it even duplicates the rows and comes back a greater number then the Client table

with 
   p as (
   select   
        Name,
        ClientId,
        GroupId,
        case
            when GroupId = 0 then Name
            when ClientId in (GroupId) and GroupId not in (0)
            then Name
            else 0
        end as Fix_Name 
    from client),
    f as (
    select  
        Name,
        GroupId,
        ClientId,
        case
            when ClientId not in (GroupId)
            then GroupId
            else ClientId
            end as ClientId1
    from client)
        select  
            p.Name,
            p.ClientId,
            p.GroupId,
            f.ClientId1,
            p.Fix_Name 
        from p left join f on (p.ClientId = f.ClientId)

CodePudding user response：

SELECT t1.Name , 
       t1.ClientId, 
       t1.GroupId,
       CASE WHEN t1.ClientId = t1.GroupId -- if ClientId and GroupId are the same 
            THEN t1.Name                  -- it displays Name as normal
            WHEN t1.GroupId = 0           -- if GroupId = 0 
            THEN t1.Name                  -- then display the name as normal
            ELSE t2.Name                  -- displays Name of the row in which it is
            END AS Fix_Name 
FROM client t1
JOIN client t2 ON t2.ClientId = t1.GroupId