SQL query to do the lookup

I have a table called “Branch” and another table called “Role” as per the screenshot and I want to have the output as mentioned in my output screenshot. I want to create a view that will give the output as mentioned in the output screen. How can I write a query which will check the branch and the role it holds and check if the role are among the roles ( Doctor, Nurse, and Plumber) and then map it against the branch in the branch table. The view should look for the branch in the role table and add a column as “Status” where it will check the role table and if any branch has all the 3 roles i.e (Doctor, Nurse, and Plumber) then add a status against that branch as “Full”, if one role of the role found then “1/3 Partial”, if two roles found then against that branch then “2/3 Partial”, if no role found or if any of the branches in the branch table is missing in role table then update the status as “Dormant” for that branch. Any help on this greatly appreciated.

Eg – As Branch “Norfolk” has all the three roles the view returns the status “Full”, As branch Cambridgeshire has two roles 2 roles it is updating the status against that branch as “2/3 partial”. As Peterborough has the role “Driver” which is not in the role of (Doctor, Nurse, and Plumber) it is marked as “Dorment” and Suffolk, Central Bedfordshire & Milton Kynes is not listed under the Role table it is also marked as “Dorment” . Any help on this would be greatly appriciated

Answer

SQL query like this probably do the job

SELECT
    b.branch,
    CASE
       WHEN COUNT(DISTINCT(r.role)) = 1 THEN 'Only one'
       WHEN COUNT(DISTINCT(r.role)) = 2 THEN '2/3 partial'
       WHEN COUNT(DISTINCT(r.role)) = 3 THEN 'Full'
       WHEN COUNT(DISTINCT(r.role)) = 0 THEN 'Dorment'
    END as role_status
FROM
   branch b LEFT JOIN role r on r.branch = b.branch WHERE r.role IN ('Doctor','Nurse','Plumber')

You don’t want to do anything like this in production environment.

I replaced column status to name role_status because if I remember right status may be a reserved word.

Advertisement

Answer