CODEWITHSUNDEEP
sql
Carlos80
Carlos80

Reputation: 433

JOIN NOT DISPLAYING ALL RECORDS

I'm sure that this is pretty obvious but I am completely stuck as I have never seen this before.

I have a data set that can vary against a list of categories (6 in total). Some clients will have 1 or 2 categories other all 6. The information I'm trying to pull through is designed to pull through all 6 categories with the corresponding information depending on the client.

So I have set up a simple mapping table with ID (1 to 6) and Category. I'm then joining this to my client data. The test data I'm using has categories 1,2,3,5 and whilst I get a NULL of 6 I get nothing for 4. I should see a Null row on category 4 as it doesn't exist in the client data?

 SELECT *

FROM .dbo.Lookup_Category C

LEFT JOIN dbo.Client CL ON C.ID = CL.Category

WHERE CL.ID = 423 OR CL.Category IS NULL

Upvotes: 0

Views: 46

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133380

Try using 

 SELECT *
 FROM .dbo.Lookup_Category C
 LEFT JOIN dbo.Client CL ON C.ID = CL.Category and ( CL.ID = 423 OR CL.Category IS NULL )

the use of where in left join is used as an inner join

Upvotes: 2

Siyual
Siyual

Reputation: 16917

Move your WHERE condition to your ON clause.

The WHERE executes after the LEFT JOIN and will filter out your NULL values for CL.ID.

SELECT *
FROM .dbo.Lookup_Category C
LEFT JOIN dbo.Client CL ON C.ID = CL.Category
                        AND (CL.ID = 423 OR CL.Category IS NULL)

Upvotes: 2

Related Questions