Get users which have multiple entries in multiple categories

The following query returns no results, I’m guessing because I’m ‘and’ing twice in a row on the same value. If I change it to ‘or’, then I get 2/3rds of what I need but I need users that have multiple entries in multiple categories – is there a way to effectively say “give me the users who have count greater than 1 in category A AND category B” in SQL?

SELECT TOP (20) UserId     
FROM PaymentRecord pr
inner join Transactions t on t.Entity = pr.UserId
where t.Category = 'CardSwipe' and t.Category = 'EFT'
group by pr.UserId
having count(t.CreatedAt) > 1

Answer

Use aggregation:

select top (20) t.Entity as UserId
from Transactions t
where t.Category in ( 'CardSwipe',  'EFT')
group by t.Entity
having count(distinct t.Category) = 2;

As you have written the query, the Users table is not necessary — unless there are “entities” in transactions that are not users, so the join is for filtering.