sql – Select within select where condition exists

Question:

Good morning,

With a table of articles with fields

Reference | Quantity | Price | AssociateReferences |

I intended to have a select that did the following, with the example:

Example Table of Articles

Item 1: X – 2 – 1€ – A, B
Item 2: Y – 5 – 2€ – C
Item 3: A – 1 – 5€ – NULL
Item 4: B – 7 – €3 – NULL
Item 5: C – 4 – 4€ – NULL

Intended 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 underlines with the fields of each associated reference. How can I do this?

Answer:

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

The 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 this relationship.

Or simply:

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 .

Scroll to Top