We help IT Professionals succeed at work.

SQL Left Join not showing the results I expect

I am trying to find missing entries for any give date in a query

For basic purposes I an only using two tables.
1: DateDim table that is populated with dates for the last 17 years  with a date field
2: Transaction Table that has a date field .

The Transaction table is missing the entire month for a certain user and the datedim table has dates that are populated up to today.

When I do a Left Outer Join datedim on transaction table I am expecting to get Null values for all of March, but it is returning only date values that the transactions table have

select top 10 Transdate,date from Dates D Left outer join Transactions T  on d.date =t.transdate
where Professionals = 'stombk0A8;1JLM' and Year(date) = '2015'
order by date desc

Open in new window


Results:
Transdate      date
2015-02-28 00:00:00.000      2015-02-28 00:00:00.000                
2015-02-28 00:00:00.000      2015-02-28 00:00:00.000
2015-02-27 00:00:00.000      2015-02-27 00:00:00.000
2015-02-27 00:00:00.000      2015-02-27 00:00:00.000
2015-02-27 00:00:00.000      2015-02-27 00:00:00.000
2015-02-26 00:00:00.000      2015-02-26 00:00:00.000
2015-02-26 00:00:00.000      2015-02-26 00:00:00.000
2015-02-26 00:00:00.000      2015-02-26 00:00:00.000
2015-02-25 00:00:00.000      2015-02-25 00:00:00.000
2015-02-25 00:00:00.000      2015-02-25 00:00:00.000

What I expected to see

Date      (No column name)
2015-04-02 00:00:00.000      NULL
2015-04-01 00:00:00.000      NULL
2015-03-31 00:00:00.000      NULL
2015-03-30 00:00:00.000      NULL
2015-03-29 00:00:00.000      NULL
2015-03-28 00:00:00.000      NULL
2015-03-27 00:00:00.000      NULL
2015-03-26 00:00:00.000      NULL
2015-03-25 00:00:00.000      NULL
2015-03-24 00:00:00.000      NULL
Comment
Watch Question

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
When writing queries, always use the table or alias prefix against all references.

It might be because [Professionals] is a field in the transactions table, but I don't know which one it comes from.

Also DO NOT USE year(date) below is a MUCH MORE EFFICIENT way to achieve that filter

SELECT TOP 10
      T.Transdate
    , D.date
FROM Dates D
LEFT OUTER JOIN Transactions T
  ON d.date = t.transdate
WHERE Professionals = 'stombk0A8;1JLM' --<< WHAT TABLE????
AND D.date >= '20150101' AND D.date < '20160101'
ORDER BY D.date DESC
;

Open in new window

Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
when you are building a left join, the FROM table needs to be the table in which you have records that might not have a match in the JOINed table. In your cases, you have dates in your DateDim table that have non-matching data in the other table so you need to start with that one. Paul query is correctly written.

Otherwise, it becomes a RIGHT JOIN.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Since you clearly don't want the Professionals comparison preventing a row from being returned, its comparison should be in the LEFT join no matter which table it is from.  However, I too strongly urge you to always prefix every column when multiple tables are used in a query.


SELECT TOP (10) T.Transdate, D.date
FROM Dates D
LEFT OUTER JOIN Transactions T  ON
    D.date = T.transdate AND
    /*?T.*/ Professionals = 'stombk0A8;1JLM'
WHERE
    D.date >= '20150101' AND
    D.date < '20160101'
ORDER BY
    date DESC
yo_beeDirector of Information Technology

Author

Commented:
The Professionals Value is the unique ID for the user.
yo_beeDirector of Information Technology

Author

Commented:
Still just pulling only the values that match and not returning Null for dates that do not match.
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
can we see your current query?
yo_beeDirector of Information Technology

Author

Commented:
select t.Transdate,d.date 

from Dates D Left Outer Join Transactions T on d.date =t.transdate
where Professionals = 'stombk0A8;1JLM' and d.date > '20150101'
order by date desc

Open in new window

yo_beeDirector of Information Technology

Author

Commented:
Attached is an excel spreadsheet with both tables
DateDim and Transactions Table  and the values I am trying to join.

SQL-Date-Issue.xlsx
Senior .Net Consultant
Top Expert 2016
Commented:
You are limiting (by using the Where clause) to Professionnals = stm..

But since you are doing a left join, the Professionnals field for dates not matching is null (thus different than stm...)

You need to move this condition in the JOIN like this:

select t.Transdate,d.date 
from Dates D 
Left  Join Transactions T 
on d.date =t.transdate
and t.Professionals = 'stombk0A8;1JLM' 
where d.date > '20150101'
order by d.date desc

Open in new window

yo_beeDirector of Information Technology

Author

Commented:
Thanks that works.  I see since I was filtering both the date as well as the professionals.  Since the dateDim table does not have any professionals value the where clause is only going to give me values with that professional.

Makes sense now.

Thanks for your help
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Interesting: I put that exact change in about half a dozen posts before, the comment I started with this:
"
Since you clearly don't want the Professionals comparison preventing a row from being returned, its comparison should be in the LEFT join no matter which table it is from.
"

I guess that wasn't good enough for whatever reason.
yo_beeDirector of Information Technology

Author

Commented:
Sorry Scott I did not pick up on that I will redistribute the points
Please object and I will correct his.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Thanks!

I don't even know off the top of my head how to object though, lol.  After all this time, they'll think I do for sure, but I'm a SQL Server expert, not an e-e site expert(!).

Anyway, the acknowledgement is good enough for me.  Thanks again!
yo_beeDirector of Information Technology

Author

Commented:
Ok
I posted another question that is a amendment to this questions
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_28649758.html