Purchase Order Lines Received Short — SQL Joins Problem
Suppliers often deliver a purchase order line in several goods receipts (GRNs).
- Difficulty: Medium
- Topics: Joins, Aggregation, Subqueries
- Dialect: MySQL
- Problem: #382
Problem statement
Suppliers often deliver a purchase order line in several goods receipts (GRNs). A line is short when the total quantity received across all its GRNs is less than the quantity ordered; a line with no GRN at all is short by its whole quantity. Over-deliveries are not short.
Return po_id, line_no, sku, qty_ordered, qty_received (0 when nothing arrived) and shortfall (qty_ordered minus qty_received) for every short line. Order the rows by po_id, then by line_no.
Tables
Table: PoLine
| Column | Type |
|---|---|
| po_id | int |
| line_no | int |
| sku | varchar |
| qty_ordered | int |
Primary key: po_id, line_no.
One row per line of a purchase order.
Table: GoodsReceipt
| Column | Type |
|---|---|
| grn_id | int |
| po_id | int |
| line_no | int |
| qty_received | int |
| received_on | date |
Primary key: grn_id.
One row per delivery against a PO line; (po_id, line_no) always exists in PoLine.
Examples
Example 1
PoLine
| po_id | line_no | sku | qty_ordered |
|---|---|---|---|
| 501 | 1 | GRO-1001 | 100 |
| 501 | 2 | GRO-1002 | 60 |
| 502 | 1 | DAI-2001 | 40 |
| 502 | 2 | SNK-6001 | 200 |
| 503 | 1 | HOM-5002 | 30 |
GoodsReceipt
| grn_id | po_id | line_no | qty_received | received_on |
|---|---|---|---|---|
| 1 | 501 | 1 | 60 | 2025-02-03 |
| 2 | 501 | 1 | 40 | 2025-02-06 |
| 3 | 501 | 2 | 45 | 2025-02-03 |
| 4 | 502 | 1 | 44 | 2025-02-05 |
| 5 | 502 | 2 | 120 | 2025-02-05 |
| 6 | 502 | 2 | 50 | 2025-02-09 |
Output
| po_id | line_no | sku | qty_ordered | qty_received | shortfall |
|---|---|---|---|---|---|
| 501 | 2 | GRO-1002 | 60 | 45 | 15 |
| 502 | 2 | SNK-6001 | 200 | 170 | 30 |
| 503 | 1 | HOM-5002 | 30 | 0 | 30 |
How to solve Purchase Order Lines Received Short
The comparison is between one ordered quantity and the sum of any number of receipts, so the receipts are aggregated first: a derived table groups GoodsReceipt by (po_id, line_no) and sums the quantities. That table is LEFT JOINed to PoLine on both key columns, so every line stays — a line nothing was delivered against gets a NULL sum, which COALESCE turns into 0.
With one row per line, the filter received < qty_ordered keeps the short lines and drops exact and over-deliveries. The shortfall is a subtraction on the same row.
Joining the raw receipts and grouping afterwards (the first alternative) gives the same numbers because each line's group then holds exactly its own receipts; the HAVING clause does the filtering. A correlated scalar subquery per line works too. The common bug is joining on po_id alone, which multiplies every line by every receipt of the whole order and inflates the sums. All forms are linear in the two tables with an index on GoodsReceipt(po_id, line_no).
Reference solution (MySQL)
SELECT l.po_id, l.line_no, l.sku, l.qty_ordered,
COALESCE(r.received, 0) AS qty_received,
l.qty_ordered - COALESCE(r.received, 0) AS shortfall
FROM PoLine l
LEFT JOIN (
SELECT po_id, line_no, SUM(qty_received) AS received
FROM GoodsReceipt
GROUP BY po_id, line_no
) r ON r.po_id = l.po_id AND r.line_no = l.line_no
WHERE COALESCE(r.received, 0) < l.qty_ordered
ORDER BY l.po_id, l.line_noAnother way
SELECT l.po_id, l.line_no, l.sku, l.qty_ordered,
COALESCE(SUM(g.qty_received), 0) AS qty_received,
l.qty_ordered - COALESCE(SUM(g.qty_received), 0) AS shortfall
FROM PoLine l
LEFT JOIN GoodsReceipt g ON g.po_id = l.po_id AND g.line_no = l.line_no
GROUP BY l.po_id, l.line_no, l.sku, l.qty_ordered
HAVING COALESCE(SUM(g.qty_received), 0) < l.qty_ordered
ORDER BY l.po_id, l.line_noAnother way
SELECT po_id, line_no, sku, qty_ordered, qty_received, qty_ordered - qty_received AS shortfall FROM (
SELECT l.po_id, l.line_no, l.sku, l.qty_ordered,
(SELECT IFNULL(SUM(g.qty_received), 0) FROM GoodsReceipt g WHERE g.po_id = l.po_id AND g.line_no = l.line_no) AS qty_received
FROM PoLine l) t
WHERE qty_received < qty_ordered
ORDER BY po_id, line_no← Supplier Average Lead Time in Days · Store Stock-Out Rate From Daily Closing Stock →