Select that respects all values in a list

Asked

Viewed 671 times

1

I need to set up a very simple select, but I’m having a hard time.

SELECT SEQPRODUTO 
FROM MRL_PRODUTOEMPRESA 
WHERE STATUSCOMPRA = 'I' 
AND NROEMPRESA IN (1,2,3,4,5,6,7,8,9,11,13,14)

I need to select the products where the STATUSCOMPRA = 'I' in all companies within the following list. But I only need the items that are inactive in EVERY company on that list.

Using the IN as I did, the result can bring products that are inactive only in a company on that list.

I believe the operator IN is not right for what I want. inserir a descrição da imagem aqui

sql oracle

3 answers

0

Simply filter all inactive products first and then add this to the existing query.

SELECT SEQPRODUTO 
FROM MRL_PRODUTOEMPRESA 
WHERE (NROEMPRESA = 1 AND STATUSCOMPRA = 'I')
      OR 
      (NROEMPRESA = 2 AND STATUSCOMPRA = 'I')
      OR 
      (NROEMPRESA = 3 AND STATUSCOMPRA = 'I')
      OR 
      (NROEMPRESA = 4 AND STATUSCOMPRA = 'I')
      OR 
      (NROEMPRESA = 5 AND STATUSCOMPRA = 'I')
      OR 
      (NROEMPRESA = 6 AND STATUSCOMPRA = 'I')
      OR 
      (NROEMPRESA = 7 AND STATUSCOMPRA = 'I')
      OR 
      (NROEMPRESA = 8 AND STATUSCOMPRA = 'I')
      OR 
      (NROEMPRESA = 9 AND STATUSCOMPRA = 'I')
      OR 
      (NROEMPRESA = 11 AND STATUSCOMPRA = 'I')
      OR 
      (NROEMPRESA = 13 AND STATUSCOMPRA = 'I')
      OR 
      (NROEMPRESA = 14 AND STATUSCOMPRA = 'I')

  • Alexandre, with this sql is still returning me products that are Active in some company of that list

  • Got it, so with this last change of the select I made it should work.

  • Alexandre, using the operator OR the select will bring products that are Inactive in some of these companies. I tested it here and that’s what happened, but it still brings me products that are active in some of these companies. What I need is to view products that are INACTIVE in all stores I’m putting on this list.

  • Celio, in the form of SELECT, if it still returns products that are active, the parentheses are probably not placed in the correct way. For in this filter there is no return STATUSCOMPRA other than "I".

  • Alexandre, I copied the SELECT I pasted here in PL exactly the way you put it. But it still brings me active product in some store. For example: You have returned a product that is INACTIVE in company 3. But this same product is active for company 5. In a way, it respected the condition ... OR (NROEMPRESA = 3 AND STATUSCOMPRA = 'I') but I need the product to be INACTIVE in all companies on the list

  • Celio, Filter only this product by code, this will help to discover the problem. And test with the filters that we apply.

Show 1 more comment

0

All I had to do was invert the query, selecting the assets or that are in the defined companies, and also invert the in to not in. Follow the Query:

SELECT 
    x.*
FROM mrl_produtoempresa x
WHERE  x.statuscompra = 'I' 
AND  x.nroempresa in (1,3,5,8)
AND  x.seqproduto NOT IN
                        (
                         SELECT
                             y.seqproduto
                         FROM mrl_produtoempresa y
                         WHERE y.statuscompra = 'A' 
                         AND  y.nroempresa IN (1,3,5,8)
                        )

I put in Sqlfiddle to help: http://sqlfiddle.com/#! 6/78151/24

0

I believe the solution you need is this:

SELECT
  mp1.*
FROM
  MRL_PRODUTOEMPRESA AS mp1
WHERE
  mp1.SEQPRODUTO = 30733
GROUP BY
  mp1.SEQPRODUTO
HAVING
  COUNT(*) = (
    SELECT
      COUNT(*)
    FROM
      MRL_PRODUTOEMPRESA AS mp2
    WHERE
      mp2.SEQPRODUTO = mp1.SEQPRODUTO
      AND mp2.STATUSCOMPRA = 'I'
  );

I put in the Fiddle: http://sqlfiddle.com/#! 9/36b0624/10

Browser other questions tagged sql oracle

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