NULL values on a LEFT JOIN

Question

NULL values on a LEFT JOIN

SQLPain

Hall of Fame

Points: 3522
More actions
April 14, 2016 at 1:15 pm

#325645

I am unable to get rows where ad.StatusAppDetail IS NULL. I am using the following code.
SELECT DISTINCT
ISNULL(Other, 0) AS Other
FROM Chain AS C
LEFT JOIN (SELECT
Count(ad.AppID) as Other,
C.DealerID
FROM chain c
LEFT JOIN App a on c.ChannelID = a.SourceID
LEFT JOIN Appd ad on a.AppID = ad.AppID
Where (ad.StatusAppDetail IS NULL) OR
(ad.StatusAppDetail <>'D' and
ad.StatusAppDetail <>'I' and
ad.StatusAppDetail <>'A')
GROUP BY C.DealerID) AS Other ON C.DealerID = Other.DealerID

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

SQLPain (4/14/2016)
I am unable to get rows where ad.StatusAppDetail IS NULL. I am using the following code.
SELECT DISTINCT
ISNULL(Other, 0) AS Other
FROM Chain AS C
LEFT JOIN (SELECT
Count(ad.AppID) as Other,
C.DealerID
FROM chain c
LEFT JOIN App a on c.ChannelID = a.SourceID
LEFT JOIN Appd ad on a.AppID = ad.AppID
Where (ad.StatusAppDetail IS NULL) OR
(ad.StatusAppDetail <>'D' and
ad.StatusAppDetail <>'I' and
ad.StatusAppDetail <>'A')
GROUP BY C.DealerID) AS Other ON C.DealerID = Other.DealerID

What do you mean by unable to get the rows? You're not getting rows where the count is 0?

Do you have an example?

Why not keep it simple and use this query?

SELECT DISTINCT

Count(ad.AppID) as Other

FROM chain c

LEFT JOIN App a on c.ChannelID = a.SourceID

LEFT JOIN Appd ad on a.AppID = ad.AppID

Where (ad.StatusAppDetail IS NULL) OR

(ad.StatusAppDetail <>'D' and

ad.StatusAppDetail <>'I' and

ad.StatusAppDetail <>'A')

GROUP BY C.DealerID;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

jeff.mason SSCrazy Eights Points: 9635 More actions · Answer 2

SQLPain (4/14/2016)
I am unable to get rows where ad.StatusAppDetail IS NULL. I am using the following code.
SELECT DISTINCT
ISNULL(Other, 0) AS Other
FROM Chain AS C
LEFT JOIN (SELECT
Count(ad.AppID) as Other,
C.DealerID
FROM chain c
LEFT JOIN App a on c.ChannelID = a.SourceID
LEFT JOIN Appd ad on a.AppID = ad.AppID
Where (ad.StatusAppDetail IS NULL) OR
(ad.StatusAppDetail <>'D' and
ad.StatusAppDetail <>'I' and
ad.StatusAppDetail <>'A')
GROUP BY C.DealerID) AS Other ON C.DealerID = Other.DealerID

You do realize that the Appd table is on the right side of the LEFT JOIN, not the left, and therefore will never be NULL unless there is a NULL In the actual values (not likely if you are using the columns for a join)? The only way that will work in theory is if you make it a RIGHT join, but without sample data we will be hard pressed to tell you exactly how to write it. But the way you wrote that, you will never get NULL for that column.