StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POin an inner join how to select only one row from the table on right based on a sliding criteria?
text
Body
copied!<p>I have a set of tables containing weeks, products, inventory and weekly forecasts from which I want to select week X product inventory and latest forecasts. But I just cannot get my hands around the SQL:</p> <pre><code>create table products ( product_id integer ); create table inventory ( product_id integer, asof_week integer, qoh float8 ); create table forecast ( product_id integer, for_week integer, asof_week integer, projection float8 ); create table weeks ( wkno integer ); insert into weeks values (4),(5),(6),(7); insert into products values(1),(2); insert into inventory values(1,5,10),(1,6,20),(2,6,200); insert into forecast values(1,4,1,10),(1,4,2,11),(1,4,3,12),(1,4,4,13), (1,5,1,11),(1,5,2,11),(1,5,3,21),(1,5,4,31), --corr:one too many (1,6,1,10),(1,6,2,11),(1,6,3,12),(1,6,4,22),(1,6,5,32),(1,6,5,42),(1,6,6,42), (1,6,1,10),(1,6,2,11),(1,6,3,12),(1,6,4,22),(1,6,5,42),(1,6,6,42), (1,7,1,10),(1,7,6,16), (2,6,5,2000),(2,7,5,2100),(2,8,5,30); </code></pre> <p>And a query:</p> <pre><code>select p.product_id "product", i.asof_week "inven asof", i.qoh "qoh", f.for_week "fcast for", f.projection "fcast qty", f.asof_week "fcast asof" from weeks w, products p left join inventory i on(p.product_id = i.product_id) left join forecast f on(p.product_id = f.product_id) where (i.asof_week is null or i.asof_week = w.wkno) and (f.for_week is null or f.for_week = w.wkno) and (f.asof_week is null or f.asof_week = (select max(f2.asof_week) from forecast f2 where f2.product_id = f.product_id and f2.for_week = f.for_week)) order by p.product_id, i.asof_week, f.for_week, f.asof_week </code></pre> <p>For example, for weeks 4-7, I am looking for a resultset:</p> <pre><code>product week qoh projection 1 4 - 13 1 5 10 31 1 6 20 42 1 7 - 16 2 6 200 2000 2 7 - 2100 </code></pre> <p>BUT in reality I get only 3 rows:</p> <pre><code> product | inven asof | qoh | fcast for | fcast qty | fcast asof ---------+------------+-----+-----------+-----------+------------ 1 | 5 | 10 | 5 | 31 | 4 1 | 6 | 20 | 6 | 42 | 6 2 | 6 | 200 | 6 | 2000 | 5 (3 rows) Time: 2.531 ms </code></pre> <p>I am rather new to SQL and can use some helpful pointers.</p> <p>Some notes on data: I have several other data tables to join which I have omitted from the example to stay focused on this problem, at least one of them similar in nature to the forecasted quantity table (i.e., with multiple version rows for every product x week). There are about 100 forecast rows for every product X week so somewhere I will also have to worry about efficiency... but correct results first.</p> <p>I am on postgresql 9.2.</p> <p>Thanks.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload