Stumped on OUTER join. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Stumped on OUTER join.

Author

Topic

ATG
Starting Member

35 Posts

Posted - 2012-08-23 : 16:04:06

I'm working on a stored procedure that dumps every day between 2 provided dates into a temp table, named #PMDailyLogs. Once that table is created, I'm joining it to an existing table which has some matching values but not all. Here is the code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DCCMonth]
(
@BegDate bDate,
@EndDate bDate,
@Project bProject
)

as

create table #PMDailyLogs
(
LogDate DATETIME NULL
)

declare @dt datetime
set @dt = @BegDate

while @dt<=@EndDate
BEGIN
insert #PMDailyLogs values (@dt)
set @dt = DATEADD(DAY, 1, @dt)
END

select #PMDailyLogs.LogDate

from #PMDailyLogs
LEFT OUTER JOIN PMDD on PMDD.LogDate=#PMDailyLogs.LogDate

where PMDD.Project='120127.' and PMDD.PMCo=1

order by #PMDailyLogs.LogDate

drop table #PMDailyLogs;

I'm stumped because when the PMDD table is not included in the code, everyday is shown, however when I do include PMDD (joined on LogDate for both tables) it only shows values that exist in PMDD. It seems the OUTER clause is not doing anything.

Thoughts?

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-23 : 16:11:25

Make this: PMDD.LogDate=#PMDailyLogs.LogDate
look like this: #PMDailyLogs.LogDate=PMDD.LogDate

-Chad

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-08-23 : 16:21:37

When you use the non-outer table in the WHERE clause, it effectively (mathematically) makes it an INNER JOIN. Perhaps you want to add the WHERE logic to the ON logic:[CODE]select #PMDailyLogs.LogDate
from #PMDailyLogs
LEFT OUTER JOIN PMDD
on PMDD.LogDate=#PMDailyLogs.LogDate
and PMDD.Project='120127.'
and PMDD.PMCo=1
order by #PMDailyLogs.LogDate[/CODE]

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry

ATG
Starting Member

35 Posts

Posted - 2012-08-23 : 16:34:39

Thanks Bustaz. That worked. That seems odd though. I feel like I've done a million OUTER joins and have never encountered this issue. Could it be something to do with it accessing a temp table?

ATG
Starting Member

35 Posts

Posted - 2012-08-23 : 16:48:28

Nevermind. I see what you mean. At one point i had tried adding

(PMDD.LogDate is null or not(PMDD.LogDate is null))

which I thought would get around that, but it didn't :(

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources