0

I have 2 tables.

1) subscription_types

 | type |
  'type1'
  'type2'

2) subscriptions

| user | sub_type | subscribed |
  'U1'   'type1'    'Y'
  'U1'   'type2'    'Y'
  'U2'   'type1'    'Y'

From this ERD you can see that user 'U2' does not have entry for subscription 'type2'. I'd like to have a report of users who do not have subscription record.

So far I have:

 select user, count(1) 
 from subscriptions 
 group by user
 having count(1) != (select count(1) from subscription_types);

Which gives me a list of users that do not have all subscriptions. But not which ones they are missing.

I've tried many different variations of join queries where the subscription_type is null, no luck.

I was hoping the following query would solve it but alas it did not

 select user, sub_type
 from subscriptions 
 group by user, sub_type
 having sub_type not in (select type from subscription_types);
  • 1
    Why do you need subscriptions.subscribed if the fact of subscription is represented well enough simply by the presence of a record in that table? – mustaccio Jul 26 at 23:53
  • @mustaccio I didn't build the tables. Not sure why previous dev did it that way – Ruslan Jul 27 at 12:07
2

It seems to me that you want the cartesian product between users and subscription_types, minus the subscriptions:

select distinct s.user, st.subscription_types
from subscriptions s
cross join subscription_types st
except
select user, sub_type
from subscriptions

This can, of course, be expressed in other ways, but this is probably the most obvious one.

2

Try this:

with 
  subscription_types (type) as 
(
  values
  'type1'
, 'type2'
)
, subscriptions (user, sub_type, subscribed) as 
( 
  values
  ('U1', 'type1', 'Y')
, ('U1', 'type2', 'Y')
, ('U2', 'type1', 'Y')
)
select u.user, t.type
from 
(
select distinct s.user 
from subscriptions s
) u, subscription_types t
where not exists (select 1 from subscriptions s where s.user=u.user and s.sub_type=t.type);

The idea is to get all User & Type combinations, and return only those which don't exist in subscriptions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.