CODEWITHSUNDEEP
sqloracle-database
Stephen Whigham
Stephen Whigham

Reputation: 3

How do I exclude multi-line set of data when ONE line of data has a specific value? SQL-ORACLE

I hope I didn't miss an answer already posted that would help me. Forgive me if I did.
Let's assume to get the below query results, I have inner joined the Item table to the Pricing table on the Item value. My current query returns something similar to this:

Item______Price
Item1_____Price1  
Item1_____Price2  
Item1_____Price3  
Item2_____Price2  
Item2_____Price3  
Item3_____Price1  
Item3_____Price2  
Item3_____Price3

I want to only return the SET (both lines) for Item 2 since Price1 does not exist for that Item. Since Item1 and Item3 contain rows that have Price1 on them, I want to exclude ALL rows for those items. I want to pull back 

Item______Price  
Item2_____Price2  
Item2_____Price3

I have tried the following on my script and it didn't return ANY results. I'm not sure what I am doing wrong and I am still very green when it comes to writing and understanding this stuff.

AND NOT EXISTS ( SELECT * FROM sqlmgr.PRICE P2 AND (P2.PRICE = 'PRICE1')

Upvotes: 0

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You need a correlated subquery for the NOT EXISTS:

NOT EXISTS (SELECT 1
            FROM sqlmgr.PRICE P2
            WHERE p2.ITEM = p.ITEM AND P2.PRICE = 'PRICE1'
           )

Your version returns nothing because there are records that have PRICE1 in them -- without the correlation piece (p2.ITEM = p.ITEM), it would check the entire table instead of just the item of interest on a given row.

Note that p is the alias of the table in the outer query, whatever that might be.

Upvotes: 2

Related Questions