Creating a many to many in postgresql

Question

I have two tables that I need to make a many to many relationship with. The one table we will call inventory is populated via a form. The other table sales is populated by importing CSVs in to the database weekly.

Example tables image

I want to step through the sales table and associate each sale row with a row with the same sku in the inventory table. Here's the kick. I need to associate only the number of sales rows indicated in the Quantity field of each Inventory row.

Example: Example image of linked tables

Now I know I can do this by creating a perl script that steps through the sales table and creates links using the ItemIDUniqueKey field in a loop based on the Quantity field. What I want to know is, is there a way to do this using SQL commands alone? I've read a lot about many to many and I've not found any one doing this.

Answer 1

Assuming tables:

create table a(
    item_id integer,
    quantity integer,
    supplier_id text,
    sku text
);

and

create table b(
    sku text,
    sale_number integer,
    item_id integer
);

following query seems to do what you want:

update b b_updated set item_id = (
    select item_id 
    from (select *, sum(quantity) over (partition by sku order by item_id) as sum from a) a 
    where 
        a.sku=b_updated.sku and 
        (a.sum)>
            (select count(1) from b b_counted 
            where 
                b_counted.sale_number<b_updated.sale_number and
                b_counted.sku=b_updated.sku
            )
    order by a.sum asc limit 1
    );