Ommited records in left outer Join?!

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

General SQL Server Forums

New to SQL Server Programming

Ommited records in left outer Join?!

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-07-13 : 11:49:06

I have this Query:

SELECT  TOP (150) a.SalesUnit, a.CoE, c.Budget, b.SALESORDERS, a.Turnover
FROM (SELECT  SalesUnit, CoE, Turnover
      FROM  dbo.Turnover2012) AS a 
LEFT OUTER JOIN
     (SELECT  SalesUnit, CoE, SALESORDERS
      FROM  dbo.SalesOrders2012) AS b ON a.SalesUnit = b.SalesUnit AND a.CoE = b.CoE
LEFT OUTER JOIN
     (SELECT  SalesUnit, CoE, Budget
      FROM  dbo.Budget2012) AS c ON a.SalesUnit = c.SalesUnit AND a.CoE = c.CoE

There are records ommited that lead to a wrong result and I don't understand how this happens nor how it can be fixed. But if I calculate the sum of Budget in a seperate Query:

SELECT SUM(Budget)
FROM dbo.Budget2012

and compare it to

SELECT  SUM(Budget)
FROM (SELECT  SalesUnit, CoE, Turnover
      FROM  dbo.Turnover2012) AS a 
LEFT OUTER JOIN
     (SELECT  SalesUnit, CoE, SALESORDERS
      FROM  dbo.SalesOrders2012) AS b ON a.SalesUnit = b.SalesUnit AND a.CoE = b.CoE
LEFT OUTER JOIN
     (SELECT  SalesUnit, CoE, Budget
      FROM  dbo.Budget2012) AS c ON a.SalesUnit = c.SalesUnit AND a.CoE = c.CoE

the result is not the same!!??

But if I place the Budget Query on 1st position in the join-chain:

SELECT  SUM(c.Budget)
FROM (SELECT  SalesUnit, CoE, Budget
      FROM  dbo.Budget2012) AS a 
LEFT OUTER JOIN
     (SELECT  SalesUnit, CoE, SALESORDERS
      FROM  dbo.SalesOrders2012) AS b ON a.SalesUnit = b.SalesUnit AND a.CoE = b.CoE
LEFT OUTER JOIN
     (SELECT  SalesUnit, CoE, Turnover
      FROM  dbo.Turnover2012) AS c ON a.SalesUnit = c.SalesUnit AND a.CoE = c.CoE

the result is correct! Unfortunately this affects other records to be ommited for the other sub-queries. I guess it is due to the NULLS for some CoE's in the Budget-table for certain SalesUnits, but I can't figure out what really happens here.

Best regards,
Martin

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-13 : 13:06:56

All records in Budget2012 have relation with Turnover2012?

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-07-13 : 13:22:27

Yes, all records do. But not vice versa. These I do expect to cause a NULL in the left outer join.[CODE]SalesOrderTable:
SalesUnit CoE SalesOrder
area01 UPG 200
area01 LVL 50

BudgetTable:
SalesUnit CoE Budget
area01 LVL 100

So the left outer join would be:
SalesUnit CoE SalesOrder Budget
area01 UPG 200 NULL
area01 LVL 50 100[/CODE]

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-07-13 : 13:30:51

There aren't any doubles, all tables had been priorly grouped by SalesUnit,CoE, so no record should be ommitted or enter twice in the joined table, wheather there is a match or not. And thus the Totals for Turnover, Budget and SalesOrder should stay the same... but the do not!?

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-13 : 13:33:53

if you execute the both query above you get the same result?

SELECT count(1)
FROM (SELECT SalesUnit, CoE, Turnover
FROM dbo.Turnover2012) AS a
LEFT OUTER JOIN
(SELECT SalesUnit, CoE, SALESORDERS
FROM dbo.SalesOrders2012) AS b ON a.SalesUnit = b.SalesUnit AND a.CoE = b.CoE
LEFT OUTER JOIN
(SELECT SalesUnit, CoE, Budget
FROM dbo.Budget2012) AS c ON a.SalesUnit = c.SalesUnit AND a.CoE = c.CoE

--
SELECT count(1)
FROM (SELECT SalesUnit, CoE, Turnover
FROM dbo.Turnover2012) AS a

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-07-13 : 13:39:13

I'll check that out now... I'm at home now, and have to get connected first, adapt the queries and check. Thank you for your interest... I'll be back soon

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-07-13 : 13:47:43

Yes, the queries give the same result, which is the amount of records in Turnover; while Budget has some less

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-07-14 : 09:52:18

I solved this by doing:
[CODE]SELECT TOT.SalesUnit, TOT.CoE, SUM(TOT.Budget), SUM(TOT.SALESORDERS), SUM(TOT.Turnover)
FROM ((SELECT SalesUnit, CoE, NULL as Budget,NULL as Salesorders,Turnover
FROM dbo.Turnover2012)
UNION ALL
(SELECT SalesUnit, CoE, NULL,SALESORDERS, NULL
FROM dbo.SalesOrders2012)
UNION ALL
(SELECT SalesUnit, CoE, Budget,NULL,NULL
FROM dbo.Budget2012)) as TOT
GROUP BY TOT.SalesUnit, TOT.CoE[/CODE]

Compared to the first query I tried it returns 6 records more; still can't explain the dynamics that causes these 6 records to be ommitted, but at leastI have the correct figures.

Martin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources