Select product structure
Good Morning!
Is there any way to create a query that brings the "product" and all the "components and subcomponents" of the structure of this product?
Let me try to explain better
Structure
Picture
Picture Link: /images/content/320848/674c45520716279c748ec8dd2150ec4e.jpg
Text format
- Bicicleta
|
+ - Guidão
|
+ - Quadro
| |
| + - Garfo
|
+ - Banco
| |
| + - Selim
|
+ - Roda
|
+ - Aro
|
+ - Raio
|
+ - Pneu
|
+ - Camara
I have the structure of the equipment " bicycle" inserted in the database and I need to get who is the "product" and everyone that is below (components[eCOMP] and subcomponents [eCOMP]) linking everyone to the main product
Example
+--------+----------+--------+----------+
|eCOD |eDESC2 |eCOMP | eDESC2 |
+--------+----------+--------+----------+
|01.0001 |Bicicleta |01.0002 | Guidão |
|01.0001 |Bicicleta |01.0003 | Quadro |
|01.0001 |Bicicleta |01.0004 | Garfo |
|01.0001 |Bicicleta |01.0005 | Banco |
|01.0001 |Bicicleta |01.0006 | Selim |
|01.0001 |Bicicleta |01.0007 | Roda |
|01.0001 |Bicicleta |01.0008 | Aro |
|01.0001 |Bicicleta |01.0009 | Raio |
|01.0001 |Bicicleta |01.0010 | Pneu |
|01.0001 |Bicicleta |01.0011 | Camara |
+--------+----------+--------+----------+
Table structure
Dice in SQLFiddle
Data in text format
Cadastro de Produto [Tabela PROD]
pCOD pDESC
01.0001 Bicicleta
01.0002 Guidão
01.0003 Quadro
01.0004 Garfo
01.0005 Banco
01.0006 Selim
01.0007 Roda
01.0008 Aro
01.0009 Raio
01.0010 Pneu
01.0011 Camara
Estrutura do Produto [Tabela ESTR]
eCOD eCOMP eQTD eNiv
01.0001 01.0002 1 1
01.0001 01.0003 1 1
01.0003 01.0004 1 2
01.0001 01.0005 1 1
01.0005 01.0006 1 2
01.0001 01.0007 2 1
01.0007 01.0008 1 2
01.0008 01.0009 1 3
01.0008 01.0010 1 3
01.0010 01.0011 1 4
Data in SQL format
CREATE TABLE PROD (
pCOD varchar(15),
pDESC varchar(100)
);
insert into PROD (pCOD, pDESC) values
('01.0001','Bicicleta'),
('01.0002','Guidão'),
('01.0003','Quadro'),
('01.0004','Garfo'),
('01.0005','Banco'),
('01.0006','Selim'),
('01.0007','Roda'),
('01.0008','Aro'),
('01.0009','Raio'),
('01.0010','Pneu'),
('01.0011','Camara')
CREATE TABLE ESTR (
eCOD varchar(15),
eCOMP varchar(15),
eQTD integer,
eNIV integer
);
insert into ESTR (eCOD, eCOMP, eQTD, eNIV) values
('01.0001','01.0002','1','1'),
('01.0001','01.0003','1','1'),
('01.0003','01.0004','1','2'),
('01.0001','01.0005','1','1'),
('01.0005','01.0006','1','2'),
('01.0001','01.0007','2','1'),
('01.0007','01.0008','1','2'),
('01.0008','01.0009','1','3'),
('01.0008','01.0010','1','3'),
('01.0010','01.0011','1','4')
1 answers
You can reach the result you want in two ways: creating a temporary table and filling it, line by line, from a CURSOR
(or loop
), which is more laborious because you will have to do the query for each level and in terms of performance (by default) is slower, or simply uses CTE
:
;WITH REC AS
(
SELECT E.*
FROM ESTR E
INNER JOIN PROD P ON P.pCOD = E.eCOD
WHERE E.eNIV = 1
UNION ALL
SELECT R.eCOD, E.eCOMP, E.eQTD, E.eNIV
FROM ESTR E
INNER JOIN REC R ON R.ecomp = E.ecod
)
SELECT R.eCOD
, P1.pDESC
, R.eCOMP
, P2.pDESC
, R.eNIV
FROM REC R
INNER JOIN PROD P1 ON P1.pCOD = R.eCOD
INNER JOIN PROD P2 ON P2.pCOD = R.eCOMP
ORDER BY R.eCOD
, R.eNIV
You can test here at SQLFiddle.