Geek asks and answers

Correlate GROUP BY and LEFT JOIN according to several criteria to show the last record?

In a simple inventory management database, the quantity of new stocks is added and shipped until the quantity reaches zero. Each material movement is assigned a link, only the last link is used.

In the given example, the last links are never displayed, the stock identifier 1.4 should have charlie, foxtrot links, respectively, but instead show alpha, delta.

How can I match GROUP BY and LEFT JOIN according to several criteria to show the last 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
+5
source share
1 answer

, :

SELECT g.parent_id, g.quantity, customer.reference
FROM (
    SELECT parent_id, SUM(stock.quantity) as quantity, MAX(id) as LatestID
    FROM stock
    GROUP BY parent_id
) g LEFT JOIN stock_reference AS custome
    ON g.LatestID = customer.stock_id AND stock_reference_type_id = 1
+1

More articles:


All Articles