Subject Re: Basic Firebird SQL question
Author vladman992000
--- In, 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,coalesce(select sum(quantity) from warehouses where
> 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'

Part ABC
Warehouse 1
Qty 5

Part ABC
Warehouse 2
Qty 3


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

Does this explain it better?