--- In firebird-support@yahoogroups.com, Ali Gökçen <alig@e...> wrote:

> many2many relation? are you sure?
> i think it is a one2many relation.
> because parts must have unique part records, but
> one part may be so many time in any warehouse.
>
> if so then try this:
>
> insert into stock_on_hand(part_id,Quantity)
> select p.id,coalesce(select sum(quantity) from warehouses where
> part_id=p.id and is_input=1),0)
> from parts p

I may not have explained this well. The Parts table has 1,000 or so
rows. The warehouses table has 6 rows. What the Stock_on_hand table
does is to represent the join between these two tables, that resolves
the many to many relationship that could occur (ie. Part ABC is found
in 3 warehouses, Warehouse 1 has 5 in stock, Warehouse 2 has 3 in
stock, etc.).

Hence in this example:

Part 'ABC'

Stock_on_hand
Part ABC
Warehouse 1
Qty 5

Stock_on_hand
Part ABC
Warehouse 2
Qty 3

etc.

The Warehouse table is only a table of Warehouse No and description.
No Qty data is stored there.

Does this explain it better?

Myles