Two conditions in Where do not bring a result

Asked

Viewed 211 times

1

I am doing a query in Oracle Database 12C and I have a problem: I want only products that have address_1 as 'A' appear in my ResultSelect.

In the example below the item "code: 312107" it has two addresses: 'P' and the 'A' and I want my select bring all the items you own only the address 'A'.

If I filter in WHERE endereco_1 not in ('P','S','V') or endereco_1 = 'A' this product below will continue to appear.

I need a condition where if the product has ('P' + 'A') in the endereco_1 then does not appear in the resultselect, if you have only 'To' and nothing more, may appear.

inserir a descrição da imagem aqui

query oracle where oracle12c

  • Augusto, I couldn’t see your print, but see if anything in the line below helps you: SELECT * FROM TABLE A1 WHERE A1.ENDERECO = 'A' AND A1.ID NOT IN ( SELECT A2.ID FROM TABLE A2 WHERE A2.ADDRESS IN ('P', ’S', 'V'))

1 answer

1

Try this SQL:

SELECT * FROM PRODUTOS AS PR1
WHERE ENDERECO_1='A' 
AND (SELECT COUNT(*) FROM PRODUTOS AS PR2 WHERE PR1.CODIGO=PR2.CODIGO AND ENDERECO_1<>'A')=0

Anything, just call me.

  • Fabiano, It worked SUPER RIGHT! I was validating here and I did not find products like the print. Can you explain to me the logic you used, why of COUNT and = 0 at the end ? It worked, but I’d like to understand exactly what this subquery did. You’re the guy

  • Hi @Augustos , in this section I count the records that have the same CODE in the main query and in the sub-query but that have ENDERECO_1 different from "A" and use the result of this COUNT as parameter for the main query. In other words the main query brings all products that have ENDERECO_1='A' and that have (ENDERECO_1<>'A') equal to zero.

  • Hello @Augustos does not take it badly, but if it helped you give a positive vote. Because it helps other people find this solution. Hug.

  • I’m sorry, I still don’t know how to move the forum right, but it helped yes, I gave my vote, have a finished mark or something? Thanks for everything!

  • No problem @Augustos, the idea is to vote positive in questions and answers that are useful and negative if they are not. Hug.

Browser other questions tagged query oracle where oracle12c

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