Data disappears when I JOIN the final table - SQL

Question

I have this query I need to complete where I need to return the Name of the customers who brought items on a certain date and the name of each item.

However, whenever I JOIN the customer table to the other tables it essentially comes back as NULL. There is definitely data in there.

I've tried an array of different join types but none seem to link in and retain the customer info. It seems like something wrong with my join types... maybe.

Thanks in advance team!

SELECT DISTINCT PM.Name AS [ProductModel Name], P.FirstName AS [Customer Name]
FROM AdventureWorksDB.Production.TransactionHistory TH 
    FULL JOIN AdventureWorksDB.Production.Product PP 
    ON TH.ProductID = PP.ProductID 
    FULL JOIN AdventureWorksDB.Production.ProductModel PM 
    ON PP.ProductModelID = PM.ProductModelID
    FULL JOIN AdventureWorksDB.Purchasing.ProductVendor PV
    ON PP.ProductID = PV.ProductID
    FULL JOIN AdventureWorksDB.Purchasing.Vendor V
    ON PV.BusinessEntityID = V.BusinessEntityID
    FULL JOIN AdventureWorksDB.Person.BusinessEntity BE
    ON V.BusinessEntityID = BE.BusinessEntityID
    FULL OUTER JOIN AdventureWorksDB.Person.Person P
    ON BE.BusinessEntityID = P.BusinessEntityID


WHERE PP.SellStartDate = '2007-07-01'

And the output:



ModelName               |Customer Name
--------------------------------------
All-Purpose Bike Stand  | NULL
Bike Wash               | NULL
Chain                   | NULL
Classic Vest            | NULL
Fender Set - Mountain   | NULL
Front Brakes            | NULL
Front Derailleur        | NULL
Hitch Rack - 4-Bike     | NULL
HL Bottom Bracket       | NULL
...
etc.

score 2 · Answer 1 · answered Jun 07 '20 at 10:06

Move the restriction in the WHERE clause to the ON clause of the appropriate join:

SELECT DISTINCT PM.Name AS [ProductModel Name], P.FirstName AS [Customer Name]
FROM AdventureWorksDB.Production.TransactionHistory TH 
FULL JOIN AdventureWorksDB.Production.Product PP
    ON TH.ProductID = PP.ProductID AND
       PP.SellStartDate = '2007-07-01'
-- rest of your current query

Your current WHERE clause will filter off all records from the Product table not having a sell start date of July 1, 2007.

score 1 · Answer 2 · answered Jun 07 '20 at 11:21

FULL JOIN is almost never used -- and almost never needed to traverse a well defined a proper data model. In this case, it is totally inappropriate because you need all the columns filled in to answer the question.

You are getting NULL values because the of all those FULL JOINs. Unmatched rows produce NULL values.

I need to return the Name of the customers who brought items on a certain date and the name of each item.

So, use INNER JOIN. The SELECT DISTINCT is only necessary if one customer purchased the same product more than once on that date. I would start with a simple SELECT:

SELECT PM.Name AS [ProductModel Name], P.FirstName AS [Customer Name]
FROM AdventureWorksDB.Production.TransactionHistory TH JOIN
     AdventureWorksDB.Production.Product PP 
     ON TH.ProductID = PP.ProductID JOIN
     AdventureWorksDB.Production.ProductModel PM 
     ON PP.ProductModelID = PM.ProductModelID JOIN
     AdventureWorksDB.Purchasing.ProductVendor PV
     ON PP.ProductID = PV.ProductID JOIN
     AdventureWorksDB.Purchasing.Vendor V
     ON PV.BusinessEntityID = V.BusinessEntityID JOIN
     AdventureWorksDB.Person.BusinessEntity BE
     ON V.BusinessEntityID = BE.BusinessEntityID
     AdventureWorksDB.Person.Person P
     ON BE.BusinessEntityID = P.BusinessEntityID
WHERE PP.SellStartDate = '2007-07-01'

Note: This is using the logic you are providing in the query. Based on the question, though, I would expect the WHERE clause to be:

WHERE CONVERT(DATE, TH.TransactionDate) = '2007-07-01'

Data disappears when I JOIN the final table - SQL

2 Answers2