I have a table called SubDivision which holds a
Then I have a table called CustomerSubDivisionXREF which holds this
My storedproc is this
csxref.SubDivisionID as "Assigned"
from [SubDivision] sub
left join [CustomerSubDivisionXREF] csxref on csxref.SubDivisionID = sub.SubDivisionID
Or use a subquery:
SELECT sub.SubDivisionID, sub.SubDivisionName, csxref.SubDivisionID AS Assigned FROM SubDivision AS sub LEFT JOIN ( SELECT t.SubDivision FROM CustomerSubDivisionXREF AS t WHERE t.CustomerID = 9 ) AS csxref ON sub.SubDivisionID = cxsref.SubDivisionID
To me this tends to be a bit more readable than the multi-predicate
on clause as you can clearly see that only the subquery's records are the ones restricted, though I'm unsure if one performs better than the other out of the box.