SQLSERVER compare two groups of results

Navigation
3

I need to query the sql server where I have to "connect" inventory to the companies.

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

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

I need to select all stocks that have at least 1 product that category equals one of the company 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 view stock 1, since one of its categories hits the category of one of the products in the stock.

Category Category 

IDCATEGORIA
NMCATEGORIA

Inventory table 

IDESTOQUE
NMESTOQUE

Product Table 

IDPRODUTO
IDESTOQUE
NMPRODUTO
IDCATEGORIA

Company Table 

IDEMPRESA
NMEMPRESA

CompanyCategory table 

IDEMPRESA
IDCATEGORIA

I hope I have been clear.

Thank you in advance.

    
asked by anonymous 03.11.2017 / 20:21

2 answers

1

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)
    
04.11.2017 / 03:27
0

I confess that it is still nebulous what you want, but maybe this helps: 

select T4.NMEMPRESA, T1.NMESTOQUE
from ESTOQUE T1
 inner join PRODUTO T2 on T1.IDESTOQUE = T2.IDESTOQUE
 inner join EMPRESACATEGORIA T3 on T2.IDCATEGORIA = T3.IDCATEGORIA
 inner join EMPRESA T4 on T3.IDEMPRESA = T4.IDEMPRESA

If anything is missing here, please comment.

    
03.11.2017 / 21:22
nth-child selection in a table with even columns [duplicate] Pass an object between scenes (screens) JavaFx