CODEWITHSUNDEEP
sqlsql-server-2008
unknown6708
unknown6708

Reputation: 55

SQL Server : join tables result doesn't contain null values (I want it to)

I have 3 tables...

Person:

PersonID   FirstName   LastName
-------------------------------
   2       Jim         Smith
   4       David       Dill
   5       Sarah       Hill
   6       Eric        Ericson

Pledge:

PersonID   Amount   Year
------------------------
   4        1000    2017
   5        1000    2017
   6        1000    2017

Contributions:

PersonID  TotalToPresent  Year
------------------------------
   4          200         2017
   5          400         2017

I want to join the tables, with those who've pledged to be listed, despite having not given.

Result:

PersonID    Pledge    TotalToPresent  Year
------------------------------------------
   4         1000          200        2017
   5         1000          400        2017
   6         1000      NULL (or zero) 2017

However, my resultant table never includes anyone who has not given (is not present in contribution table)

My query:

SELECT 
    Person.PersonID,
    PLEDGES.PledgeAmount,
    PLEDGES.ContributionYear,
    Person.LastName,
    Person.FirstName,
    Person.PrimaryAssociationID,
    Contributions.TotalContrib
FROM 
    PLEDGES
INNER JOIN 
    (Contributions 
LEFT JOIN 
    Person ON Contributions.PersonID = Person.PersonID) 
    ON PLEDGES.PersonID = Person.PersonID 
FROM 
    PLEDGES 
INNER JOIN 
    Person ON PLEDGES.PersonID = Person.PersonID 
INNER JOIN 
    Contributions ON Person.PersonID = Contributions.PersonID 
WHERE  
    PLEDGES.Year = '2017' 
ORDER BY 
    Person.PersonID

Upvotes: 3

Views: 71

Answers (2)

user1464246
user1464246

Reputation: 125

First off, put your sql through a formatter / beautifier. It'll make your life lots easier.

Now for your question: an inner join means that it'll only show matches. What you're looking for is a left join. So do something like this:

select
    *
from 
    table1 t1
    left join table2 t2
        t1.key = t2.foreign_key
    left join table3 t3
        t2.key = t3.foreign_key
order by
    whatever

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520938

You should INNER JOIN the Person table to PLEDGES, to filter off anyone who has not even pledged. Then do a LEFT JOIN to Contributions to possibly include information about contributions which a person made, beyond just having pledged.

SELECT t1.PersonID,
       t2.PledgeAmount,
       t2.ContributionYear,
       t1.LastName,
       t1.FirstName,
       t1.PrimaryAssociationID,
       COALESCE(t3.TotalContrib, 0.0) AS TotalContrib
FROM Person
INNER JOIN PLEDGES t2
    ON t1.PersonID = t2.PersonID
LEFT JOIN Contributions t3
    ON t1.PersonID = t3.PersonID

Note that you might also want to join PLEDGES to Contributions using the year, if your data could have more than one year. I assumed in my answer that a given person has at most one entry in PLEDGES and Contributions.

Upvotes: 1

Related Questions