Left Outer Join not working correctly

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 2005 Forums

Transact-SQL (2005)

Left Outer Join not working correctly

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-06 : 19:52:20

Hi

I am using the following final select in SQL 2005. I want to show the values for ExecteToCoverDiff_OLD and ResponseCount_OLD as NULL when the ExecteToCoverDiff_ALL and ResponseCount_ALL match ExecteToCoverDiff_NEW and ResponseCount_NEW. Same is the case when ExecteToCoverDiff_ALL and ResponseCount_ALL values match ExecteToCoverDiff_OLD and ResponseCount_OLD values, the ExecteToCoverDiff_NEW and ResponseCount_NEW values should be NULL which I believe is the additional purpose of using Outer joins.

Currently for matched values of ExecteToCoverDiff_ALL
and ResponseCount_ALL with ExecteToCoverDiff_OLD
ResponseCount_OLD , ExecteToCoverDiff_NEW
ResponseCount_NEW values show NULL which is great. But for matched values of ExecteToCoverDiff_ALL ,ResponseCount_ALL
and ExecteToCoverDiff_NEW
ResponseCount_NEW values, I expect the ExecteToCoverDiff_OLD ,ResponseCount_OLD values to be NULL. Instead it shows values. Similarly, this pattern continues even for the other case where earlier I had NULL values.

What is wrong? Should I include and If..statement to force NULL values when this is the case? Should I use temp tables instead?

Note that the order in the #AllDealer is by TradeYear and then TradeMonth.

Any help is appreciated. Thanks a bunch

select
alld.TradeMonthName,
alld.TradeYear,
alld.ExecuteToCoverDiff ,
alld.ResponseCount ,
New.ExecuteToCoverDiff ,
New.ResponseCount ,
Old.ExecuteToCoverDiff ,
Old.ResponseCount ,
alld.TradeMonth ,
alld.QuarterByYear,
alld.DealerTypeDate
from
#AllDealer alld
LEFT OUTER JOIN
#NewDealer New
ON
alld.TradeMonthName = New.TradeMonthName
AND alld.TradeYear = New.TradeYear
LEFT OUTER JOIN
#OldDealer Old
ON
alld.TradeMonthName = Old.TradeMonthName
AND alld.TradeYear = Old.TradeYear

The data is as below: I cold not format to fit in the page but hopefully the column names and values are readable.

The columns are as below

TradeMonthName
TradeYear
ExecteToCoverDiff_ALL
ResponseCount_ALL
ExecteToCoverDiff_OLD
ResponseCount_OLD
ExecteToCoverDiff_NEW
ResponseCount_NEW
TradeMonth
QuarterByYear
DealerTypeDate

January 2009 24.4241149147541 3.28196721311475 NULL NULL 24.4241149147541 3.28196721311475 01 Q1 OLD
February 2009 24.6610613651376 3.04770642201835 NULL NULL 24.6610613651376 3.04770642201835 02 Q1 OLD
March 2009 21.4489570791075 3.06896551724138 NULL NULL 21.4489570791075 3.06896551724138 03 Q1 OLD
April 2009 22.797960170068 3.2687074829932 NULL NULL 22.797960170068 3.2687074829932 04 Q2 OLD
May 2009 25.1883597044335 3.33497536945813 NULL NULL 25.1883597044335 3.33497536945813 05 Q2 OLD
June 2009 19.4323144223827 3.76173285198556 NULL NULL 19.4323144223827 3.76173285198556 06 Q2 OLD
July 2009 71.918232464455 4.05687203791469 NULL NULL 71.918232464455 4.05687203791469 07 Q3 OLD
August 2009 18.2744382670808 3.82298136645963 NULL NULL 18.2744382670808 3.82298136645963 08 Q3 OLD
September 2009 14.3352155945946 4.17027027027027 NULL NULL 14.3352155945946 4.17027027027027 09 Q3 OLD
October 2009 0.200000000000003 9.5 0.200000000000003 9.5 23.3343639556962 4.51898734177215 10 Q4 NEW
October 2009 23.3343639556962 4.51898734177215 0.200000000000003 9.5 23.3343639556962 4.51898734177215 10 Q4 OLD
November 2009 6.06 3.6 6.06 3.6 17.0942585403424 4.18807339449541 11 Q4 NEW
November 2009 17.0942585403424 4.18807339449541 6.06 3.6 17.0942585403424 4.18807339449541 11 Q4 OLD
December 2009 13.3842857142857 4.90476190476191 13.3842857142857 4.90476190476191 12.4545201873831 4.20089285714286 12 Q4 NEW
December 2009 12.4545201873831 4.20089285714286 13.3842857142857 4.90476190476191 12.4545201873831 4.20089285714286 12 Q4 OLD
January 2010 11.4792311538462 3.92307692307692 11.4792311538462 3.92307692307692 17.6750053985208 3.98975409836066 01 Q1 NEW
January 2010 17.6750053985208 3.98975409836066 11.4792311538462 3.92307692307692 17.6750053985208 3.98975409836066 01 Q1 OLD

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-06 : 19:59:54

I tried using a filer but to no avail. I need some way to identify the first mismatch and replace those values with NULL.

WHERE New.ExecuteToCoverDiff = CASE WHEN New.ExecuteToCoverDiff NOT IN (select alld.ExecuteToCoverDiff from
#AllDealer) THEN 'NULL'

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-06 : 23:12:52

I think I fixed it. Whew! What a relief :-)

I added the DealerTypeDate which holds values 'Old' and 'New' to teh #OldDealer and #Newdealer tables and grouped by the DealerTypeDate field as well. Then I added the condition where All.DealerTypeDate = New.DealerTypeDate and where All.DealerTypeDate = Old.DealerTypeDate to both LEFT OUTER JOINS in the final select. Now I see NULL for values that do not match.

select
alld.TradeMonthName,
alld.TradeYear,
alld.ExecuteToCoverDiff ,
alld.ResponseCount ,
New.ExecuteToCoverDiff ,
New.ResponseCount ,
Old.ExecuteToCoverDiff ,
Old.ResponseCount ,
alld.TradeMonth ,
alld.QuarterByYear,
alld.DealerTypeDate
from
#AllDealer alld
LEFT OUTER JOIN
#NewDealer New
ON
alld.TradeMonthName = New.TradeMonthName
AND alld.TradeYear = New.TradeYear
AND alld.DealerTypeDate = New.DealerTypeDate
LEFT OUTER JOIN
#OldDealer Old
ON
alld.TradeMonthName = Old.TradeMonthName
AND alld.TradeYear = Old.TradeYear
AND alld.DealerTypeDate = Old.DealerTypeDate

Trade Trade Execute Response Execute Response Execute Response Trade Quarter Dealer
Month Year To Count_ALL To Count_NEW To Count_OLD Month By Type
Name CoverDiff_ALL CoverDiff_NEW CoverDiff_OLD YearDate

January 2009 24.4241149147541 3.28196721311475 NULL NULL 24.4241149147541 3.28196721311475 01 Q1 OLD
February 2009 24.6610613651376 3.04770642201835 NULL NULL 24.6610613651376 3.04770642201835 02 Q1 OLD
March 2009 21.4489570791075 3.06896551724138 NULL NULL 21.4489570791075 3.06896551724138 03 Q1 OLD
April 2009 22.797960170068 3.2687074829932 NULL NULL 22.797960170068 3.2687074829932 04 Q2 OLD
May 2009 25.1883597044335 3.33497536945813 NULL NULL 25.1883597044335 3.33497536945813 05 Q2 OLD
June 2009 19.4323144223827 3.76173285198556 NULL NULL 19.4323144223827 3.76173285198556 06 Q2 OLD
July 2009 71.918232464455 4.05687203791469 NULL NULL 71.918232464455 4.05687203791469 07 Q3 OLD
August 2009 18.2744382670808 3.82298136645963 NULL NULL 18.2744382670808 3.82298136645963 08 Q3 OLD
September 2009 14.3352155945946 4.17027027027027 NULL NULL 14.3352155945946 4.17027027027027 09 Q3 OLD
October 2009 0.200000000000003 9.5 0.200000000000003 9.5 NULL NULL 10 Q4 NEW
October 2009 23.3343639556962 4.51898734177215 NULL NULL 23.3343639556962 4.51898734177215 10 Q4 OLD
November 2009 6.06 3.6 6.06 3.6 NULL NULL 11 Q4 NEW
November 2009 17.0942585403424 4.18807339449541 NULL NULL 17.0942585403424 4.18807339449541 11 Q4 OLD
December 2009 13.3842857142857 4.90476190476191 13.3842857142857 4.90476190476191 NULL NULL 12 Q4 NEW
December 2009 12.4545201873831 4.20089285714286 NULL NULL 12.4545201873831 4.20089285714286 12 Q4 OLD
January 2010 11.4792311538462 3.92307692307692 11.4792311538462 3.92307692307692 NULL NULL 01 Q1 NEW
January 2010 17.6750053985208 3.98975409836066 NULL NULL 17.6750053985208 3.98975409836066 01 Q1 OLD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources