A protocol is a set of services. A clinic offers services, independently of protocols. Should a clinic offer all those services, it satisfies a protocol. Return a list of all satisfied protocols given a single clinic.
(Pardon the spacing and syntax, but blogspot nukes my tabs and spaces. I must figure out a better way to post code.)
SELECTWhat the query does is to say - give me all Protocols, except the ones where the service list is not satisfied. The subquery looks at the join tables (they are named just as they behave, per naming conventions) and determines any Protocols where the Clinic is not satisfying all Services - that is, where there is a null in the LEFT JOIN on ClinicServices.
p.IsActive = 1
AND p.ID NOT IN (
ps.ServiceID = cs.ServiceID
and cs.IsActive = 1
and cs.clinicid = 987
ps.IsActive = 1
and cs.ID is null
Now, here's the fun part.
The developer I was working with, despite all his talents, is a groupie for lambda expressions, which I understand are some sort of dark magic concocted in obsidian towers for purposes dire and contradictory to the good will of databases.
Despite all that hyperbole, here's what he came up with to convert the above query to the below nonsense, err, λ expression. From what he explained to me, Lambda expressions are dynamic and use the declaratives seen below while LINQ is more hard coded. I welcome further input with the caveat that LINQ, EF and other systems for generating bad TSQL are systema non grata around here. :)
var protocols = Protocols.Where(p => p.IsActive);
var activeProtocolServices = ProtocolServices.Where(ps => ps.IsActive);
var clinicServices = ClinicServices
.Where(cs => cs.IsActive)
.Where(cs => cs.ClinicID == 987);
var allProtocolServicesForClinics = activeProtocolServices.Join(clinicServices, ps => ps.ServiceID, cs => cs.ServiceID, (ps, cs) => ps);
var protocolServicesNotApplicableForClinic = activeProtocolServices.Except(allProtocolServicesForClinics);
var protocolsForClinic = protocols.Except(protocolServicesNotApplicableForClinic.Select(ps => ps.Protocol));