Post reply

Subquery in Case When Statement

  • March 17, 2022 at 6:40 pm

    Go to Answer

    #4001453

    I'm writing a view that is expressing cumulative data between a parent table and child table where the parent table has a one to many relationship with the child table.  The child table has a column 'Status' which may contain the values 'Pending', 'Blocked', 'Allowed' or there may be no records in the child table for that parent item.

    In the view, if a single record in the child table (as related to the parent) has a 'Blocked' status the view should show 'Blocked'.  If that doesn't exist, if an 'Allowed' exists, show 'Allowed'.  If that doesn't exist, if 'Pending' exists show 'Pending' and if no records exist show null.

    Best I've got so far is a series of exists statements and it seems so ..... hacky

    case when exists (select top 1 * from Visiting.UserSummary s where users.id=s.recordid and s.status = 'Blocked')
    		THEN 'Blocked'
    		WHEN  exists (select top 1 * from Visiting.UserSummary s where users.id=s.recordid and s.status = 'Allowed')
    		THEN 'Allowed'
    		WHEN  exists (select top 1 * from Visiting.UserSummary s where users.id=s.recordid and s.status = 'Pending')
    		THEN 'Pending'
    		ElSE ''
    end as UserStatus

  • March 17, 2022 at 7:58 pm

    Answer

    #4001540 

    SELECT *
      FROM ParentTable p
     OUTER APPLY(SELECT TOP(1) *
                   FROM ChildTable c
                  WHERE c.ParentId = p.Id
                  ORDER BY CASE c.Status 
                               WHEN 'Blocked' THEN 1
                               WHEN 'Allowed' THEN 2
                               WHEN 'Pending' THEN 3
                               ELSE 4
                            END
                ) c

  • March 18, 2022 at 12:09 pm

    #4001881

    Nice!  Thank you.  I just like seeing stuff done better!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply