Chris Chris - 1 year ago 51
SQL Question

Get data whether the id is there or not

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

This returns all the SubDivisions and null for the Assigned, which is fine, and looks like this..
What is returned

However, once I introduce the

where csxref.CustomerID = 9

Then nothing gets returned, and I understand why. But the question is, how do I get all the SubDivisions, regardless if there is any assigned to a customer? So I guess in short I would like to pass a CustomerID and if there is no SubDivisions assigned to that CustomerID then I would like it to still return what I show in the picture.


I forgot to mention that I have to pass a CustomerID into this storedproc.

Answer Source

Or use a subquery:

SELECT sub.SubDivisionID, sub.SubDivisionName, csxref.SubDivisionID AS Assigned
FROM SubDivision AS sub
    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.