I'm starting with sqlite and would like help with a recursive query.
I have 3 tables (generic example):
CREATE TABLE IF NOT EXISTS tab_componente (
id_comp TEXT NOT NULL PRIMARY KEY,
desc_comp TEXT ,
custo_comp INTEGER);
CREATE TABLE IF NOT EXISTS tab_montagem (
id_mont TEXT NOT NULL PRIMARY KEY,
desc_mont TEXT);
CREATE TABLE IF NOT EXISTS tab_mont_itens (
id_item TEXT NOT NULL,
id_parent TEXT NOT NULL,
tipo TEXT,
quant INTEGER NOT NULL);
INSERT INTO tab_componente(id_comp, desc_comp, custo_comp)
VALUES ('comp1', 'componente 1', 1.00),
('comp2', 'componente 2', 1.00),
('comp3', 'componente 3', 1.00),
('comp4', 'componente 4', 1.00),
('comp5', 'componente 5', 1.00),
('comp6', 'componente 6', 1.00),
('comp7', 'componente 7', 2.00),
('comp8', 'componente 8', 1.00);
INSERT INTO tab_montagem(id_mont, desc_mont)
VALUES ('mont1', 'montagem 1'),
('mont2', 'montagem 2'),
('mont3', 'montagem 3'),
('mont4', 'montagem 4'),
('mont5', 'montagem 5');
INSERT INTO tab_mont_itens (id_item, id_parent, tipo, quant)
VALUES
('comp1', 'mont1', 'componente' ,1.00),
('comp2', 'mont1', 'componente', 1.00),
('comp3', 'mont1', 'componente', 1.00),
('comp2', 'mont2', 'componente', 1.00),
('comp4', 'mont3', 'componente' ,1.00),
('comp5', 'mont3', 'componente', 1.00),
('comp7', 'mont3', 'componente', 1.00),
('comp6', 'mont4', 'componente', 1.00),
('comp8', 'mont4', 'componente', 1.00),
('comp7', 'mont5', 'componente' ,1.00),
('mont4', 'mont2', 'montagem', 2.00),
('mont4', 'mont3', 'montagem', 3.00),
('mont5', 'mont2', 'montagem', 1.00),
('mont5', 'mont3', 'montagem', 1.00);
This is a tree structure with mounts and sub-assemblies:
-
mont1
-
comp1
-
comp2
-
comp3
-
-
mont2
-
comp2
-
mont4 (quant = 2)
-
comp6
-
comp8
-
-
mont5
- comp7
-
-
mont3
-
comp4
-
comp5
-
comp7
-
mont4 (quant = 3)
-
comp6
-
comp8
-
-
mont5
- comp7
-
The recursive query for a specific assembly works correctly showing the sum of the components and sub-components, for example:
WITH RECURSIVE descendentes AS
(SELECT id_parent AS parent, id_item AS descendente, quant, tipo FROM tab_mont_itens
UNION ALL
SELECT d.parent, p.id_item, p.quant*d.quant, p.tipo
FROM descendentes AS d
INNER JOIN tab_mont_itens AS p
ON d.descendente = p.id_parent)
SELECT parent,descendente, SUM(quant), tipo FROM descendentes
WHERE parent = 'mont2' AND tipo = 'componente'
GROUP BY descendente
But my goal is to show the total cost per component, with all assemblies, so I tried the following way:
SELECT id_comp,custo_comp, t.soma_comp,(custo_comp*t.soma_comp) AS custo FROM tab_componente
INNER JOIN
(WITH RECURSIVE descendentes AS (
SELECT id_parent AS parent, id_item AS descendente, quant, tipo FROM tab_mont_itens
UNION ALL
SELECT d.parent, p.id_item, p.quant*d.quant, p.tipo
FROM descendentes AS d
INNER JOIN tab_mont_itens AS p
ON d.descendente = p.id_parent)
SELECT descendente, SUM(quant) AS soma_comp, tipo FROM descendentes
WHERE tipo = 'componente'
GROUP BY descendente) AS t ON id_comp = t.descendente
GROUP BY id_comp
But the result shows the correct sums of components 1 through 5 and components 6, 7, and 8 with erroneous values (by adding +1?).
Can anyone tell me what I'm doing wrong, or give me a hint on how to do it otherwise?