Product Structure Select

Question

Product Structure Select

Navigation

#1 by (2 votes)

3

Good morning!

Is there a way to create a query that brings the "product" and all "Components and Subcomponents" of the structure of this product?

Let me try to explain better

Structure

Image

LinktoImage: link

Text format

- Bicicleta
  |
  + - Guidão
  |
  + - Quadro
  |   |
  |   + - Garfo
  |
  + - Banco
  |   |
  |   + - Selim
  |
  + - Roda
      |
      + - Aro
          |
          + - Raio
          |
          + - Pneu
              |
              + - Camara

I have the structure of the "bicycle" equipment inserted in the database and I need to get the "product" and all that are below (Components [eCOMP] and Subcomponents [eCOMP]) linking all 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

Data 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

SQL Format Data

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')

sql sql-server sql-server-2012

asked by anonymous 08.08.2018 / 13:48

1 answer

GIT does not upa new or changed files remote location Problem with jQuery mouseleave function to trigger popup

score 2 · Accepted Answer

You can get the result you want in two ways: by creating a temporary table and filling row 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 in SQLFiddle .