I would like to display the 1st table. The CreditTotal should be displaying the sum from table 2 where Table2.InventoryID matches Table1.ID

The first row CreditTotal should be showing 2000. The second row CreditTotal should be displaying 600. I have no clue how to do it. Do you? Thanks.





Table 1: Inventory
ID-----Company Name-----------CreditTotal
1 -------- Company A ------------------
2 -------- Company B ------------------



Table 2: Credit
ID ---InventoryID ---------- Credit
1 ----------- 1 ------------------ 1200
2 ----------- 1 -------------------600
3 ----------- 1 -------------------200
4 ----------- 2 -------------------500
5 ----------- 2 -------------------100

use a join and a GROUP BY

SELECT companyname,sum(cred.credit) as ct from credit as cred left join inventory on inventory.id = cred.inventoryid group by companyname ;

NOTE:
Avoid using a field with the same name as the table, it will cause ambiguity errors when using some types of queries.

wow, thanks .
I was monkeying around with this for many hours now. I modified your code to right join so that it displays null values also.

If I may.... ask one more thing: There is another column in Table1 called TotalValue. Again the total needs to be summed up from table2 where Table1.ID = Table2.InventoryID

Totalvalue on row 1 should be 60 and 40 on row 2. I appriciate your help.


Table 1: Inventory
ID-----Company Name-----------CreditTotal------------TotalValue
1 -------- Company A ------------------ 2000----------------------
2 -------- Company B ------------------ 600------------------------



Table 2: Credit
ID ---InventoryID ---------- Credit-------Value
1 ----------- 1 ------------------ 1200------------20
2 ----------- 1 -------------------600-------------20
3 ----------- 1 -------------------200-------------20
4 ----------- 2 -------------------500-------------20
5 ----------- 2 -------------------100-------------20

nevermind, I got it.

Thanks again.