MySql column summation across tables

BlueSpud

Hi,

I want to define a table by item and location to store the number of despatches for a given item at a given location. Can I add code to item_location.num_despatches so that it will count the number of despatches of that item at that location. In other words what I want to be able to do is to have my application maintain Item, Location, Despatch etc but have MySql maintain item_location.num_despatches.

Item
----
id {p/k}
name

Location

---

id {p/k}
name


Item_Location

---

id {p/k}
item_id {f/k for item on item.id }
location_id {f/k for location on location.id }
num_despatches

Despatch

---

id {p/k}
item_id {f/k for item on item.id }
location_id {f/k for location on location.id }
item_location_id {f/k for item_location on item_location.id }
etc

On insert of a despatch, I want the field item_location.num_despatches to be incremented by 1, and I want to have the database do it rather than coding it in the application.

JonathanAnon

I'm familiar with mysql, but this posting is just too vague for me to be able to help... what relationships are there between the tables, what exactly do the fields relate to?? Could you run through what currently happens when you track a dispatch.. ?

BlueSpud

Ok, more details added. Hope this make it clearer.

JonathanAnon

How are you creating the database and tables. are you using PHPMyAdmin or something... When you set up the table, I usually set all the id / primary key field as auto_increment..



For example the above is a database for a GAA website, for displaying news stories.. I have set the storyid field to autoincrement... so when adding a new story to the database, the SQL statement would be something like

INSERT INTO stories('null', ... etc, etc etc, );

It will automatically add the story with the next available number as the storyid.

BlueSpud

Your code causes the primary ID of the table to increment, so if you have 10 records already and you add another, it will automatically get an ID of 11. What I am looking for is different, I want a counter on the ITEM-LOCATION table to count the records on another table, but only those records that are for the same ITEM and LOCATION.

FSL

If MySQL supports triggers and the id field on the despatches table is auto increment you could possibly use an insert trigger on the despatches table to increment the count in the other table using the location id from the max(id) record on the despatches table.