[SQL] Oracle SQL Need to identify distinct PK values where a grouped set contains a specified...

There are several questions that are close to this, but I haven't found that they address my situation. I have the following table, SUBSCRIPTIONS, that identifies individual amenities in a subscriber's subscription:

User Id Category Rank Method Start Date End Date Amenity
A001 A B C 01-JUN-23 01-JUN-24 A1
A001 A B C 01-JUN-23 01-JUN-24 A2
A002 A B C 15-OCT-23 15-OCT-24 A1
A002 A B C 15-OCT-23 15-OCT-24 A2
A002 A B C 15-OCT-23 15-OCT-24 A3
A003 D E F 11-NOV-23 11-NOV-24 A1

User Id is the subscriber. The combination of Category, Rank, and Method are the way the subscriber signed up for their subscription. The start date is when the subscription starts. The end date is when the subscription ends. Amenity is the individual amenity.

Unfortunately, the subscription isn't identified by a solitary serial number/ID. An individual user's subscription is the combination of User Id, Category, Rank, Method, Start Date, and End Date.

I need to identify distinct User Ids where their subscription of Category = A, Rank = B, and Method = C does not include amenity A3.

So from the above table, I would need to identify that User Id A001 had a subscription combination of A, B, and C but did not have amenity 'A3'. User Id A003 did not have 'A3' but their subscription combination was D, E, and F.

select DISTINCT User Id
from SUBSCRIPTIONS
where Category = 'A'
and Rank = 'B'
and Method = 'C'
and Amenity <> 'A3'


This is clearly wrong because it's just removing rows that have amenity 'A3'. I researched GROUPING SETS and GROUP BY CUBE, but I'm not aggregating data here so grouping expressions aren't working.

What is the proper approach for creating sets and checking for a specified value in each set?

Continue reading...