join query bringing back null values

Question

join query bringing back null values

junk.mail291276

SSCrazy

Points: 2503
More actions
February 2, 2017 at 11:06 am

#318267

Hello,
I am trying to do a join that isn't bringing in the values as I'm expect. I'd appreciate some help figuring out why.
Here's the query:
            SELECT
                PS.ProjectId,
                PS.ProjectSafeguardId,
                PS.Description,
                SUM(S.Credit) AS TotalCredit
            FROM ProjectSafeguard PS
                left JOIN Safeguard S ON PS.ProjectSafeguardId = S.SafeguardId
            WHERE PS.ProjectId = 28
            GROUP BY
                PS.ProjectId,
                PS.Description,
                PS.ProjectSafeguardId
The results I get look like this:
The unexpected values I'm getting are the nulls under TotalCredit. They should not be null.
To test this, I picked the top ProjectSafeguardId of 1423 and ran this on the Safeguard table using:
SELECT
  [ProjectSafeguardId],
    [Credit]
FROM [RiskAliveDev].[dbo].[Safeguard]
where ProjectSafeguardId = 1423
and got:
So we have 10 Safeguards linked to ProjectSafeguard 1423, all with Credits 1.000, but their sum is coming through as null in the join query.
What am I doing wrong?

Viewing 5 posts - 1 through 4 (of 4 total)

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

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

junk.mail291276 - Thursday, February 2, 2017 11:06 AM
Hello,
I am trying to do a join that isn't bringing in the values as I'm expect. I'd appreciate some help figuring out why.
Here's the query:
            SELECT
                PS.ProjectId,
                PS.ProjectSafeguardId,
                PS.Description,
                SUM(S.Credit) AS TotalCredit
            FROM ProjectSafeguard PS
                left JOIN Safeguard S ON PS.ProjectSafeguardId = S.SafeguardId
            WHERE PS.ProjectId = 28
            GROUP BY
                PS.ProjectId,
                PS.Description,
                PS.ProjectSafeguardId
The results I get look like this:
The unexpected values I'm getting are the nulls under TotalCredit. They should not be null.
To test this, I picked the top ProjectSafeguardId of 1423 and ran this on the Safeguard table using:
SELECT
  [ProjectSafeguardId],
    [Credit]
FROM [RiskAliveDev].[dbo].[Safeguard]
where ProjectSafeguardId = 1423
and got:
So we have 10 Safeguards linked to ProjectSafeguard 1423, all with Credits 1.000, but their sum is coming through as null in the join query.
What am I doing wrong?

Add SafeguardID to the select list of your last query.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

drew.allen SSC Guru Points: 76988 More actions · Answer 2

Your join is on PS.ProjectSafeguardID = S.SafeguardID, but your select is on S.ProjectSafeguardID. Change your join to PS.ProjectSafeguardID = S.ProjectSafeguardID.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

What are the datatypes of ProjectSafeguardId and S.SafeguardId?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

junk.mail291276 SSCrazy Points: 2503 More actions · Answer 4

drew.allen - Thursday, February 2, 2017 11:20 AM
Your join is on PS.ProjectSafeguardID = S.SafeguardID, but your select is on S.ProjectSafeguardID. Change your join to PS.ProjectSafeguardID = S.ProjectSafeguardID.
Drew

d'Oh! That's it. Amazing what your eye won't catch (well, my eye anyway).

Thanks for pointing out the obvious! LOL