Correlate GROUP BY and LEFT JOIN on multiple criteria to show latest record?
- by Sunbird
In a simple stock management database, quantity of new stock is added and shipped until quantity reaches zero. Each stock movement is assigned a reference, only the latest reference is used.
In the example provided, the latest references are never shown, the stock ID's 1,4 should have references charlie, foxtrot respectively, but instead show alpha, delta.
How can a GROUP BY and LEFT JOIN on multiple criteria be correlated to show the latest record?
http://sqlfiddle.com/#!2/6bf37/107
CREATE TABLE stock (
id tinyint PRIMARY KEY,
quantity int,
parent_id tinyint
);
CREATE TABLE stock_reference (
id tinyint PRIMARY KEY,
stock_id tinyint,
stock_reference_type_id tinyint,
reference varchar(50)
);
CREATE TABLE stock_reference_type (
id tinyint PRIMARY KEY,
name varchar(50)
);
INSERT INTO stock VALUES
(1, 10, 1),
(2, -5, 1),
(3, -5, 1),
(4, 20, 4),
(5, -10, 4),
(6, -5, 4);
INSERT INTO stock_reference VALUES
(1, 1, 1, 'Alpha'),
(2, 2, 1, 'Beta'),
(3, 3, 1, 'Charlie'),
(4, 4, 1, 'Delta'),
(5, 5, 1, 'Echo'),
(6, 6, 1, 'Foxtrot');
INSERT INTO stock_reference_type VALUES
(1, 'Customer Reference');
SELECT stock.id, SUM(stock.quantity) as quantity, customer.reference
FROM stock
LEFT JOIN stock_reference AS customer ON stock.id = customer.stock_id AND stock_reference_type_id = 1
GROUP BY stock.parent_id
