I have 2 tables.
| type | 'type1' 'type2'
| 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);