Querying All Records of All Users who have any instance of purchase behavior in their history

Question

I am trying to write a query that shows me all user ids that in any instance of usage have a particular type of "purchase credit" associated with their account. I have one table, "users" that shows user information, ie, user ids, names, birthdate, etc. and then I have another table that shows all users all instances of credit purchases. I want to pull all credit purchases (not just the type I am looking up) from all users who in any instance of purchase have this one kind of credit purchase in their record. Any help would be much appreciated!

Answer 1

It's common practice to write this sort of query using an exists construct, something like:

select *
from users join credit_purchases using (user_id)
where purchase_type = 'A'
and exists (
    select 'x'
    from credit_purchases
    where purchase_type = 'B'
    and user_id = users.user_id
);

Be careful that this type of query only works properly if you're looking for different records in the sub-query; if it is at all possible to "hit" the same record, you need to exclude that in the subquery's where clause, by doing something like:

select *
from users join credit_purchases cp using (user_id)
where purchase_type = 'A'
and exists (
    select 'x'
    from credit_purchases
    where purchase_type = 'A'
    and user_id = users.user_id
    and credit_purchase_id <> cp.credit_purchase_id
);

I'm assuming your tables have surrogate keys with the obvious names.