Why this isnt work incorrect?

[beeper]

I have database with tables like this:
http://img244.imageshack.us/img244/7411/a5if.jpg

I want to know how many cash is floating in contract
A report must look like that:
ID_PK | SUM(costs.Value) | SUM(foreincosts.Value) | SUM(service.Value)

I have example data like this:
Contract
http://img200.imageshack.us/img200/1673/19vp.jpg

Costs
http://img179.imageshack.us/img179/8407/23dm.jpg

ForeinCosts
http://img105.imageshack.us/img105/5049/23zd.jpg

Service
http://img169.imageshack.us/img169/3752/40mj.jpg

Query looks like this.


SELECT
contract.HowMutch,
SUM(costs.Value) AS CostSum,
SUM(foreincosts.Value) AS FCostSum,
SUM(service.Value) AS ServSum,
contract.ID_PK
FROM
contract
LEFT OUTER JOIN costs ON (contract.ID_PK=costs.IDcontract_FK)
LEFT OUTER JOIN foreincosts ON (contract.ID_PK=foreincosts.ID_Contract_FK)
LEFT OUTER JOIN service ON (contract.ID_PK=service.ID_Contract_FK)
GROUP BY
contract.ID_PK

But this is incorrect results. What is wrong with this query??
Please help.
http://img280.imageshack.us/img280/2450/55jm.jpg

[olivthill]

The "LEFT OUTER JOIN" clause is the one to have when you want to select all the records of the left table and the records of the right table having a match with the left table. So usually, a left outer join is chosen when the left table is bigger than the right table.
But I suppose the results are not those you expect because, with your example, you have 2 right tables having more records than the left table. The solution is to use a RIGHT OUTER JOIN clause for these tables, or maybe it's better in your case to use an INNER JOIN clause.

[olivthill]

Excuse-me, I see again your tables, and I'm making a mistake. My previous post does not apply to your situation.

I rather think the problem is caused by GROUP BY contract.ID_PK.
You should have one Group by, for each of your tables. But there is a little difficulty, because, of course, you can't put these GROUP BY at the end of your SELECT statement. I'm going to search a way to have these three necessary GROUP BY in one Select statement.

[olivthill]

Here is the the right SQL

SELECT [contract].[HowMutch],
(SELECT sum([costs].[Value]) FROM costs
WHERE costs.IDcontract_FK = contract.ID_PK
GROUP BY [costs].[IDcontract_FK]) AS CostSum,
(SELECT sum([foreincosts].[Value]) FROM foreincosts
WHERE foreincosts.ID_Contract_FK = contract.ID_PK
GROUP BY [foreincosts].[ID_Contract_FK]) AS FCostSum,
(SELECT sum([service].[Value]) FROM service
WHERE service.ID_Contract_FK = contract.ID_PK
GROUP BY [service].[ID_Contract_FK]) AS ServSum
FROM contract;

[beeper]

olivthill (member.php?u=171621) - big thx for that. Work just great :)