SQL - Using CASE in a JOIN

SQL - Using CASE in a JOIN

We have constantly issues with different kinds of customers and based on their status or payment history, you want to join them to the loyalty tables. The focus was to come up with a solution that minimises the extra reads on the other tables but also to add this to a stored proc to minimise modifications to the procedure if it arises

So at the end of the day

 JOIN  dbSecurity.dbo.AccountInstSecurityRole s
  ON s.InstitutionID =  
     CASE
       WHEN (RecordCount) <= 1
         THEN v.ParentInstitutionID
       ELSE v.InstitutionID
     END

Here is the full example

USE dbTechnikons

-- Gets all the child records for the intitution
SELECT
  v.InstitutionID,
  v.Name,
  v.HierarchyLevelID,
  v.HierarchyLevelName,
  v.Disabled,
  v.CompanyID,
  v.ParentInstitutionID
FROM
    vInstitution v
  JOIN
    dbSecurity.dbo.AccountInstSecurityRole s
  ON s.InstitutionID =
     -- If count is 1 or less, then is unrestricted,
     -- otherwise, different join
     CASE
       WHEN (SELECT
               COUNT(*)
             FROM
                 vInstitution v
               JOIN
                 dbSecurity.dbo.AccountInstSecurityRole s
               ON (v.InstitutionID = s.InstitutionID) 
             WHERE
                 v.ParentInstitutionID = @ProviderInstitution
               AND
                 s.AccountID = @LoginID
               AND
                 v.HierarchyLevelID > 1
               AND
                 v.Disabled = 0
            ) <= 1
         THEN v.ParentInstitutionID
       ELSE v.InstitutionID
     END

-- Based this on the Technikon ID that was passed through, LoginID
WHERE
    v.ParentInstitutionID = @ProviderInstitution
  AND
    s.AccountID = @LoginID
  AND
    v.HierarchyLevelID > 1
  AND
    v.Disabled = 0
ORDER BY
   v.Name