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
LVL 26
yo_beeDirector of Information TechnologyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
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

0
Éric MoreauSenior .Net ConsultantCommented:
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.
0
Scott PletcherSenior DBACommented:
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
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

yo_beeDirector of Information TechnologyAuthor Commented:
The Professionals Value is the unique ID for the user.
0
yo_beeDirector of Information TechnologyAuthor Commented:
Still just pulling only the values that match and not returning Null for dates that do not match.
0
Éric MoreauSenior .Net ConsultantCommented:
can we see your current query?
0
yo_beeDirector of Information TechnologyAuthor 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

0
yo_beeDirector of Information TechnologyAuthor 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
0
Éric MoreauSenior .Net ConsultantCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yo_beeDirector of Information TechnologyAuthor 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
0
Scott PletcherSenior DBACommented:
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.
0
yo_beeDirector of Information TechnologyAuthor Commented:
Sorry Scott I did not pick up on that I will redistribute the points
Please object and I will correct his.
0
Scott PletcherSenior DBACommented:
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!
0
yo_beeDirector of Information TechnologyAuthor 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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.