Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE inventory (the_date date, good text, quantity int);
INSERT INTO inventory VALUES
  ('2013-08-09', 'egg'  , 5)
, ('2013-08-09', 'pear' , 7)
, ('2013-08-02', 'egg'  , 1)
, ('2013-08-02', 'pear' , 2)
;

CREATE TABLE price (the_date date, good text, price numeric);
INSERT INTO price VALUES
  ('2013-08-07','egg' , 120)
, ('2013-08-06','pear', 200)
, ('2013-08-01','egg' , 110)
, ('2013-07-30','pear', 220)
;

CREATE TABLE



INSERT 0 4



CREATE TABLE



INSERT 0 4


-- With DISTINCT ON:
SELECT DISTINCT ON (i.good, i.the_date)
       i.the_date, p.the_date AS pricing_date, i.good, i.quantity, p.price
FROM   inventory  i
LEFT   JOIN price p ON i.good = p.good AND i.the_date >= p.the_date
ORDER  BY i.good, i.the_date, p.the_date DESC;


the_date
pricing_date
good
quantity
price


2013-08-02
2013-08-01
egg
1
110

2013-08-09
2013-08-07
egg
5
120

2013-08-02
2013-07-30
pear
2
220

2013-08-09
2013-08-06
pear
7
200



SELECT 4


-- With NOT EXISTS (standard SQL, works with every RDBMS I know):
SELECT i.the_date, p.the_date AS pricing_date, i.good, i.quantity, p.price
FROM   inventory  i
LEFT   JOIN price p ON p.good = i.good AND p.the_date <= i.the_date
WHERE  NOT EXISTS (
   SELECT 1 FROM price p1
   WHERE  p1.good = p.good
   AND    p1.the_date <= i.the_date
   AND    p1.the_date >  p.the_date
   );


the_date
pricing_date
good
quantity
price


2013-08-09
2013-08-07
egg
5
120

2013-08-02
2013-08-01
egg
1
110

2013-08-09
2013-08-06
pear
7
200

2013-08-02
2013-07-30
pear
2
220



SELECT 4

the_date	pricing_date	good	quantity	price
2013-08-02	2013-08-01	egg	1	110
2013-08-09	2013-08-07	egg	5	120
2013-08-02	2013-07-30	pear	2	220
2013-08-09	2013-08-06	pear	7	200