[SOLVED] How do I get data from another column and row if certain criteria are met?

Issue

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)

Solution

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 

Answered By – Akina

Answer Checked By – David Goodson (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.