Select that respects all values in a list

Question

Select that respects all values in a list

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

1

I need to mount a very simple select, but I'm having trouble.

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 STATUSCOMPRA = 'I' in all companies within the following list. But I only need the items that are inactive in ALL the companies on that list.

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

I believe the IN operator is not the correct one for what I want.

sql oracle

asked by anonymous 15.08.2017 / 21:43

3 answers

Page content offset when closing modal bootstrap Error generating migration after changing a relationship

score 0 · Answer 1

I just invert the query by selecting the assets or that are in the defined companies, and also invert the in for not in. Follow 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 SQLFiddle to help: link

score 0 · Answer 2

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 it in the Fiddle: link

score 0 · Answer 3

Just filter all inactive products first and then add that 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')