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.

score 0 · Accepted Answer · answered Apr 21 '15 at 15:42

0

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

answered Apr 21 '15 at 15:42

Radek Postołowicz

4,506
2
30
47

Thank you for your help. I had actually written a very verbose perl script to do this by the time I got your post. I've been involved with other projects and not been able to get back to it yet. As it stands the perl scripts is very clunky and every time I need to change some thing I go back to it and forget half of it's inner workings. I am going to go through your solution here and try to implement it as this seems a much more straight forward approach. I will check back with updates. Thanks – joshw May 23 '15 at 00:54
So I finally had time to get back to this and realized that I left out an important point. Rows in table b have another column which is the quantity sold. So frankly your answer would definitely work for the initial question I posed. However, as the project moved forward I realized I had not thought out all the requirements. So I think I am stuck with the awful clunky perl script after all. – joshw Jun 09 '15 at 17:40

Creating a many to many in postgresql

1 Answers1