| Author |
 Topic  |
|
|
barnabeck
Posting Yak Master
Spain
161 Posts |
 Posted - 07/13/2012 : 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.CoEthe 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
Yak Posting Veteran
Portugal
52 Posts |
Posted - 07/13/2012 : 13:06:56
|
| All records in Budget2012 have relation with Turnover2012? |
 |
|
|
barnabeck
Posting Yak Master
Spain
161 Posts |
Posted - 07/13/2012 : 13:22:27
|
Yes, all records do. But not vice versa. These I do expect to cause a NULL in the left outer join.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 |
Edited by - barnabeck on 07/13/2012 13:25:11 |
|
|
|
barnabeck
Posting Yak Master
Spain
161 Posts |
Posted - 07/13/2012 : 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
Yak Posting Veteran
Portugal
52 Posts |
Posted - 07/13/2012 : 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
Spain
161 Posts |
Posted - 07/13/2012 : 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
Spain
161 Posts |
Posted - 07/13/2012 : 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
Spain
161 Posts |
Posted - 07/14/2012 : 09:52:18
|
I solved this by doing:
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
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 |
Edited by - barnabeck on 07/14/2012 09:54:54 |
|
|
| |
Topic |
|
|
|
Ommited records in left outer Join?!
