SQLSERVER compare two groups of results

Asked

Viewed 82 times

3

I need to make a query in sql server where I have to "connect" stocks to companies.

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

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

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

For example:

Stocking 1

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

Enterprise 1

categorias: concreto, pedra, cimento.

Enterprise 2

categorias: cimento, tijolo.

In this case, only company 1 would view stock 1, because one of its categories matches the category of one of the products within the stock.

Table Category

IDCATEGORIA
NMCATEGORIA

Stock Table

IDESTOQUE
NMESTOQUE

Product Table

IDPRODUTO
IDESTOQUE
NMPRODUTO
IDCATEGORIA

Table Company

IDEMPRESA
NMEMPRESA

Table Empresacategoria

IDEMPRESA
IDCATEGORIA

I hope I was clear.

Thanks in advance.

sql-server query
  • 1

    Diego, your question is fine, but without knowing how the tables are and the relationships gets a little complicated to assemble a query and help you! try to place the tables here, at least the part (that interests you) . abs.

  • Diego the product categories in the tables empresa 1 and empresa 2 have the same codigo that in stock table?

  • Put the structure of the tables, so we can help!

  • 1

    The category is the same table. I edited the question, if you need more data just say it. Think about using the "IN", but I’ve only used it to date from a variable in a group, not group to group.

  • Maybe I can help you.

  • An ERI would help to see the relationships between the tables.

Show 1 more comment

2 answers

1


You can apply the clause EXISTS 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)
  • 1

    Thank you, had never used this exists, worked perfectly.

0

I confess that still this nebulous what you want, but maybe it 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

In case there is something missing here in the comments.

  • What I need is the following, I have a screen that shows all the stocks(I don’t see the products, only if I click the stock I go to the screen that shows the products of the same). On this screen (the one that shows the stocks) I have to filter according to the company that is viewing, ie if I am logged in with company 2, I could not see, because between the categories that I am linked, has no category equal to one of the categories of stock products 1.

  • 1

    Replicates the structure and relationships of the table in this bd emulator http://sqlfiddle.com/ and shares the link here. This will make it easier to understand the relationships of the tables

  • Thanks for helping Anderson, Sorak’s answer solved the problem. Hugs.

Browser other questions tagged sql-server query

You are not signed in. Login or sign up in order to post.