Why this isnt work incorrect?

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.

Code:

---

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

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.

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.

Here is the the right SQL

Code:

---

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;

---

olivthill - big thx for that. Work just great :)