CODEWITHSUNDEEP
sqlgroup-byhavingoracle-fusion-middleware
user26499835
user26499835

Reputation: 1

SQL query to fetch rows which strictly has "one particular" value and no other value

I am using below SQL query to extract records from DB which has only "123" in INVENTORY_ITEM_ID column for below screenshot.

enter image description here

select subs.SUBSCRIPTION_NUMBER
FROM OSS_PRODUCT_ASSOCIATIONS opa
JOIN OSS_SUBSCRIPTIONS subs ON opa.SUBSCRIPTION_ID = subs.SUBSCRIPTION_ID
WHERE subs.SUBSCRIPTION_NUMBER IN (
    select DISTINCT SUBSCRIPTION_NUMBER 
    from OSS_SUBSCRIPTIONS 
    where status IN ('ORA_ACTIVE','ORA_UNDER_AMENDMENT','ORA_DRAFT')
  )
  AND OBJECT_TYPE='ORA_PRODUCT'
  AND INVENTORY_ITEM_ID = '123'
group by subs.SUBSCRIPTION_NUMBER

I want to fetch those SUBSCRIPTION NUMBERS which have only "123" in INVENTORY_ITEM_ID column and have no other value. But using above query I am getting 101 and 103 as well while I expect only 101 to be the result.

Upvotes: 0

Views: 35

Answers (0)

Related Questions