postgresqlmany-to-manyassociate

Creating a many to many in postgresql


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.


Solution

  • 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
        );