asked on

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

PortletPaul

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 Moreau

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 Pletcher

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_bee

ASKER

The Professionals Value is the unique ID for the user.

yo_bee

ASKER

Still just pulling only the values that match and not returning Null for dates that do not match.

Éric Moreau

can we see your current query?

yo_bee

ASKER

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_bee

ASKER

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

SQL-Date-Issue.xlsx

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

yo_bee

ASKER

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 Pletcher

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_bee

ASKER

Sorry Scott I did not pick up on that I will redistribute the points
Please object and I will correct his.

Scott Pletcher

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_bee

ASKER

Ok
I posted another question that is a amendment to this questions
https://www.experts-exchange.com/questions/28649758/How-to-populate-Left-Join-Null-Values-with-a-Group-Value.html