I have a table like below. Let's call it table1.

ID	Subscription
101	A
101	B
101	C
102	A
103	A
103	B
104	D

I would like to get the IDs of:

1. Customers have ONLY Subscription A
2. Customers with Subscription A

Bucket 1 would be = 102

Bucket 2 would be = 101, 102, 103

Note! ID=104 would not be in any bucket because it does not have Subscription A.

How can I get two lists with Bucket1 IDs? AKA IDs with only Subscription A. Note, Subscription are just examples. They are multiple Subscriptions.

The expected output could be a temporary table with only one column which has the IDs from Bucket1.

CodePudding user response：

I hope mine is readable/understandable. sqlfiddle

bucket 1:

select distinct id from docs as d1
where d1.id in (select id from docs where id=d1.id and subscription='A')
  and not exists (select id from docs where id=d1.id and subscription!='A')

bucket 2:

select distinct id from docs
where id in (select id from docs where subscription='A');

CodePudding user response：

We can use aggregation here. For customers with only subscription A:

SELECT ID
FROM yourTable
GROUP BY ID
HAVING MIN(Subscription) = MAX(Subscription) AND
       MIN(Subscription) = 'A';

For customers having subscription A possibly with other subscriptions as well:

SELECT DISTINCT ID
FROM yourTable
WHERE Subscripion = 'A';

CodePudding user response：

You can get the IDs with only subscription 'A' if they don't have any other subscription:

SELECT ID
FROM tablename
GROUP BY ID
HAVING SUM(Subscription <> 'A') = 0;

Assuming that the combination of ID and Subscription is unique, like your sample data, for the IDs with subscription 'A' and any other subscription it's simple:

SELECT ID
FROM tablename
WHERE Subscription = 'A';

There is no need for DISTINCT.

See the demo.