Chris Chris - 6 months ago 8
SQL Question

Get data whether the id is there or not

I have a table called SubDivision which holds a

SubDivisionID

SubDivisionName

Directions

Then I have a table called CustomerSubDivisionXREF which holds this

CustomerSubDivisionXREFID

SubDivisionID

CustomerID

My storedproc is this

select
sub.SubDivisionID,
sub.SubDivisionName,
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.

EDIT

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

Answer

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.

Comments