SELECT that brings products active in one company and that are not active in all others

Asked

Viewed 718 times

1

I have a table that shows products that are Active (A) or Inactive (I) in 16 branches. IE, has 16 lines for each product.

I want to select all products that are Active (A) in Enterprise 16 and at the same time must be Inactive (I) in all other companies.

How can I ride a SELECT bring me this information?

sql oracle pl-sql

  • The assets you want are only those of the code 16 company? And that if simultaneously active (in this enterprise 16) is inactive at least in any other?

  • I want to fetch products that are Active only on 16 and simultaneously inactive in all others

  • put the table structure in question, or some sample code that facilitates understanding.

2 answers

1


You can try something like this:

SELECT * FROM Tabela_Produtos p INNER JOIN Tabela_Filiais f ON f.id = p.filial_id
WHERE p.filial_id = 16 AND p.status LIKE 'Ativo' AND p.id NOT IN (
      SELECT p2.id FROM Tabela_Produtos p2 
      INNER JOIN Tabela_Filiais f2 ON f2.id = p2.filial_id
      WHERE p2.filial_id != 16 AND p2.status LIKE 'Inativo')

Whereas the data is all in one table MRL_PRODUTOEMPRESA your consultation will stay:

SELECT * FROM MRL_PRODUTOEMPRESA 
         WHERE (EMPRESA = 16 AND STATUS LIKE 'A')
         OR (EMPRESA != 16 AND STATUS LIKE 'I');

The query is simpler than the first, but be careful with the modeling of your database, because putting everything in the same table is not considered a good practice for relational databases and I do not advise, especially if it is used for professional projects.

There was no attempt on me for the detail inactive in ALL other companies. See if you can solve:

SELECT * FROM MRL_PRODUTOEMPRESA WHERE EMPRESA = 16 AND STATUS LIKE 'A' AND PRODUTO IN
     (SELECT PRODUTO FROM MRL_PRODUTOEMPRESA WHERE EMPRESA != 16 
        AND STATUS LIKE 'I' HAVING COUNT(PRODUTO) >= 
             (SELECT DISTINCT COUNT(PRODUTO)-1 FROM MRL_PRODUTOEMPRESA GROUP BY PRODUTO) 
     GROUP BY PRODUTO)
  • The above query searches all COMPANY 16 PRODUCTS with Active STATUS
  • That are not on the list of COMPANIES other than 16 with Inactive Status
  • Containing the total registered PRODUCTS minus 1 (referring to the single Active PRODUCT)
  • 1

    Ai que ta, is a table only that I have this information. Her name is MRL_PRODUTOEMPRESA. In this table I have a column with the name PRODUCT another with the name COMPANY and a column with the name STATUS (besides many others) in all other.

  • @Celiosartori follows new response in the complement... Hug!

  • 1

    We are almost there hehe... In this last select you gave me I believe that it will bring all products where the COMPANY is different from 16 and the STATUS is 'i' OR (COMPANY != 16 AND STATUS LIKE 'I'); In this case you will consult it in the table and if the company is different from 16 and the status is inactive will bring me. So far this is super correct! But I need one more detail that is ... ALL companies need to be inactive Only on active 16. For example Product = 10 Companies = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15 Status = I (Inactive) Company = 16 Status = A

  • @Celiosartori there was me attack for the inactive detail in ALL other companies. I placed a new query, check if it meets your problem.

  • 1

    Perfect! Brought exactly the products I wanted. Thank you very much partner!

0

Good night

Try something like that:

IB OBJECT_ID('TEMPDB..#TEMP_01') IS NOT NULL
   DROP TABLE #TEMP_01

SELECT A.PRODUTO,
       SUM(CASE WHEN A.SITUACAO = 'A' 
                THEN 1 
                ELSE 0 
           END
       ) AS OCORRENCIA_ATIVA
  INTO #TEMP_01
  FROM TB_PRODUTOS A
 WHERE A.LOJA <> 16


SELECT A.*
  FROM TB_PRODUTOS A
  JOIN #TEMP_01    B ON B.PRODUTO = A.PRODUTO
 WHERE A.LOJA = 16
   AND A.SITUACAO         = 'A'
   AND B.OCORRENCIA_ATIVA = 0

Browser other questions tagged sql oracle pl-sql

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