CODEWITHSUNDEEP
sqlsql-server
Chris
Chris

Reputation: 3129

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 stored procedure looks like 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 stored procedure.

Upvotes: 0

Views: 49

Answers (2)

jleach
jleach

Reputation: 7790

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270371

You need to put that condition in the on clause:

select sub.SubDivisionID, sub.SubDivisionName,
       csxref.SubDivisionID as "Assigned"
from [SubDivision] sub left join
     [CustomerSubDivisionXREF] csxref
     on csxref.SubDivisionID = sub.SubDivisionID and
        csxref.CustomerID = 9;

By putting it in the where clause, you are turning the join into an inner join -- because non-matching rows get NULL and that fails the comparison.

Upvotes: 1

Related Questions