Select within select where condition exists

Question

Good morning,

With a table of articles with the fields

Reference | Quantity | Price | Referencesociated |

I wanted to have a select that would do the following, with the example:

Example Table of Articles

Article 1: X - 2 - 1€ - A, B
Article 2: Y - 5 - 2€ - C
Artigo3: A - 1 - 5€ - NULL
Article 4: B - 7 - 3€ - NULL
Artigo5: C - 4 - 4€ - NULL

Desired result of the query:

Reference | Quantity | Price |

X | 2 | 1€ |
A | 1 | 5€ |
B | 7 | 3€ |
Y | 5 | 2€ |
C | 4 | 4€ |

If the reference has associated references, it creates underscores with the fields of each associated reference. How can I do this?

Answer 1

Considering the following structure:

CREATE TABLE artigos (
  referencia VARCHAR(1),
  quantidade INTEGER,
  preco      NUMERIC(15, 2)

);

INSERT INTO artigos(referencia, quantidade, preco)
            VALUES ('X', 2, 1.0),
                   ('Y', 5, 2.0),
                   ('A', 1, 5.0),
                   ('B', 7, 3.0),
                   ('C', 4, 4.0);

CREATE TABLE associadas(
  referencia VARCHAR(1),
  associada VARCHAR(1)
);

INSERT INTO associadas(referencia, associada)
               VALUES ('X', 'A'),
                      ('X', 'B'),
                      ('Y', 'C');

To query to get the result you want would be:

SELECT x.referencia,
       x.quantidade,
       x.preco
  FROM (
    SELECT art.referencia AS aux,
           art.referencia,
           art.quantidade,
           art.preco
      FROM artigos art
     WHERE EXISTS(SELECT 1
                    FROM associadas ass
                   WHERE ass.referencia = art.referencia)
    UNION
    SELECT ass.referencia AS aux,
           art.referencia,
           art.quantidade,
           art.preco
      FROM artigos art
     INNER JOIN associadas ass ON ass.associada = art.referencia
) x
ORDER BY x.aux,
         CASE x.aux
           WHEN x.referencia THEN 0
           ELSE 1
         END;

In the query above we would link the records to those that are hierarchically larger and we would sort by that relation.

Or by simplifying:

SELECT art.referencia,
       art.quantidade,
       art.preco
  FROM artigos art
  LEFT JOIN associadas ass ON ass.associada = art.referencia
 ORDER BY COALESCE(ass.referencia, art.referencia),
          CASE
            WHEN ass.referencia IS NULL THEN 0
            ELSE 1
          END

The result would be:

| referencia | quantidade | preco |
| ---------- | ---------- | ----- |
| X          | 2          | 1     |
| A          | 1          | 5     |
| B          | 7          | 3     |
| Y          | 5          | 2     |
| C          | 4          | 4     |

---

You can check the result in DB Fiddle for the first example or for the second example.

Answer 2

Here’s a suggestion that uses nonrecursive CTE.

-- código #1
with ctePai as (
SELECT A.Referencia, A.Quantidade, A.Preco, A.Referencia as Pai, 0 as Tag
  from tabArtigos as A
  where not exists (SELECT * from tabAssociação as B where B.RefAssociada = A.Referencia)

union all

SELECT C.RefAssociada, D.Quantidade, D.Preco, C.Referencia, 1
  from tabAssociação as C
       inner join tabArtigos as D on D.Referencia = C.RefAssociada
)
SELECT Referencia, Quantidade, Preco  --, Pai, Tag
  from ctePai 
  order by Pai, Tag;

To test, I used the following structure/data:

-- código #2
CREATE TABLE tabArtigos (
  Referencia char(1) not null primary key,
  Quantidade tinyint,
  Preco money
);

INSERT into tabArtigos (Referencia, Quantidade, Preco) values
     ('X', 2, 1), ('Y', 5, 2), ('A', 1, 5),
     ('Z', 3, 2.1), ('B', 7, 3), ('C', 4, 4);

CREATE TABLE tabAssociação (
  Referencia char(1) not null references tabArtigos (Referencia),
  RefAssociada char(1) not null references tabArtigos (Referencia)
);

INSERT into tabAssociação (Referencia, RefAssociada) values
     ('X', 'A'), ('X', 'B'), ('Y', 'C');

Note that in the data was added line with Reference value ('Z'), which has no subreferences and is not sub-reference of other.