sql-server – SQLSERVER compare two sets of results

Question:

I need to make a query on sql server where I have to "link" stocks to companies.

The stock has 'n' products linked to it, and these products have 1 category linked to it.

The company also has 'n' categories attached to it.

I need to select all stocks that have at least 1 product whose category is equal to one of the company's categories.

For example:

stock 1

produto 1 - categoria: pedra
produto 2 - categoria: brita
produto 3 - categoria: areia

Company 1

categorias: concreto, pedra, cimento.

Company 2

categorias: cimento, tijolo.

In this case, only company 1 would visualize stock 1, as one of its categories matches the category of one of the products in stock.

Category Table

IDCATEGORIA
NMCATEGORIA

Stock table

IDESTOQUE
NMESTOQUE

Product Table

IDPRODUTO
IDESTOQUE
NMPRODUTO
IDCATEGORIA

Company Table

IDEMPRESA
NMEMPRESA

Company Category Table

IDEMPRESA
IDCATEGORIA

I hope I was clear.

Thanks in advance.

Answer:

You can apply the EXISTS clause as follows:

SELECT e.*
  FROM Estoque e
 WHERE EXISTS(SELECT 1
                FROM empresacategoria ec
                     INNER JOIN produto p ON p.idcategoria = ec.idcategoria
               WHERE ec.idempresa = 1
                 AND p.idestoque = e.idestoque)
Scroll to Top