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
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
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