PostgreSQL pass value into INNER JOIN

Question

PostgreSQL 11

How to pass o.create_date value into INNER JOIN? I need Max ID before o.create_date

SELECT o.id,
       o.create_date               date,
       sum(oi.quantity)            qty,
       sum(oi.quantity * sp.price) total
FROM ax_order o
         LEFT JOIN ax_order_invenotry oi on o.id = oi.order_id
         LEFT JOIN ax_inventory i on i.id = oi.inventory_id
         LEFT JOIN ax_suppliers s on s.id = o.supplier_id
         INNER JOIN ax_supplier_price sp ON (sp.inventory_id = oi.inventory_id and sp.supplier_id = o.supplier_id)
         INNER JOIN
     (
         SELECT inventory_id,
                max(id) id
         FROM ax_supplier_price
         WHERE create_date <= o.create_date
         GROUP BY inventory_id
     ) lsp ON (sp.id = lsp.id)
WHERE o.store_id = 13
  AND o.supplier_id = 35
GROUP BY o.id, o.create_date
ORDER BY o.id

Answer 1

You could use the LATERAL join mechanism to make it work:

WITH ax_order AS (
    SELECT *
    FROM (VALUES (1, '2000-1-1'::date, 1, 1)) as x(id, create_date, store_id, supplier_id)
), ax_order_inventory AS (
    SELECT *
    FROM (VALUES (1, 2, 4)) as x(order_id, inventory_id, quantity)
), ax_supplier_price AS (
    SELECT *
    FROM (VALUES (1, 2, 1, 10, '1999-12-31'::date)) as x(id, inventory_id, supplier_id, price, create_date)
)
SELECT o.id,
       o.create_date               date,
       sum(oi.quantity)            qty,
       sum(oi.quantity * sp.price) total
FROM ax_order o
         LEFT JOIN ax_order_inventory oi on o.id = oi.order_id
         INNER JOIN ax_supplier_price sp ON (sp.inventory_id = oi.inventory_id and sp.supplier_id = o.supplier_id)
         INNER JOIN LATERAL
     (
         SELECT inventory_id,
                max(lsp.id) id
         FROM ax_supplier_price lsp
         WHERE sp.create_date <= o.create_date
         GROUP BY inventory_id
     ) lsp ON sp.id = lsp.id
GROUP BY o.id, o.create_date
ORDER BY o.id

I deleted some JOINs that were not strictly necessary and mocked your data as well as I could see. Note, however, that you could also use a WHERE clause to find it - which should be more efficient:

WITH ax_order AS (
    SELECT *
    FROM (VALUES (1, '2000-1-1'::date, 1, 1)) as x(id, create_date, store_id, supplier_id)
),
     ax_order_inventory AS (
         SELECT *
         FROM (VALUES (1, 2, 4)) as x(order_id, inventory_id, quantity)
     ),
     ax_supplier_price AS (
         SELECT *
         FROM (VALUES (1, 2, 1, 10, '1999-12-31'::date)) as x(id, inventory_id, supplier_id, price, create_date)
     )
SELECT o.id,
       o.create_date               date,
       sum(oi.quantity)            qty,
       sum(oi.quantity * sp.price) total
FROM ax_order o
         LEFT JOIN ax_order_inventory oi on o.id = oi.order_id
         INNER JOIN ax_supplier_price sp
                    ON (sp.inventory_id = oi.inventory_id and sp.supplier_id = o.supplier_id)
WHERE sp.id =
      (
          -- NOTE: no GROUP BY necessary!
          SELECT max(lsp.id) id
          FROM ax_supplier_price lsp
          WHERE sp.create_date <= o.create_date
            AND lsp.inventory_id = sp.inventory_id
      )
GROUP BY o.id, o.create_date
ORDER BY o.id