Krysvac Krysvac - 2 months ago 6
SQL Question

Update all rows on table based on value from other table that gets multiple values from third table

so I have three tables in a database on a Microsoft SQL Server.

The system is an example one for learning, and it contains table A, which is schools that have an id and a total amount of money spent on buying stuff.

Table B contains the stuff you can buy with an id and price

Table C contains all orders, and order_id, school_id, stuff_id, and amount of stuff

My problem is this, I have to create a trigger that when a value is inserted in table C updates all schools total amount of money spent. And I can't figure out how to do that. Especially since i have to count the amount of things bought aswell.

If you want to see the sql as I do you can do it here: http://sqlfiddle.com/#!6/265f0

Answer

You want a query that is like this:

select c.school_id, sum(b.price)
from b join
     c
     on b.stuff_id = b.stuff_id
group by c.school_id;

You can use triggers. But this gets much more complicated, because you have to handle deleted and updating records, as well as inserting them.