In postgres, what is the best way to do an AND with a join table?

Question

I have different categories of user, and a join table that allows users to be in more than one category. My join table is called categories_users and it consists of a user_id and a category_id.

I want to filter for users that are in both category1 and category2. For example, I want to find everyone that's interested in both baseball and football.

What is the best way to do this in PostgreSQL? I have the following working:

select * from users 
  where users.id IN 
    ( Select categories_users.user_id from categories_users 
      JOIN categories ON categories.id = categories_users.category_id 
      where categories.id = 1 OR categories.parent_id = 1) 
  AND users.id IN 
    (Select categories_users.user_id from categories_users 
    JOIN categories ON categories.id = categories_users.category_id 
    where categories.id = 2 OR categories.parent_id = 2)

However this feels clunky, and I am wondering if there is a better way to do this. I've tried a variety of joins, but always end up searching for rows in the categories_users table that have a category_id of 1 and 2, which isn't possible.

Edit, I actually need to also search on category parent, so I've changed the above query to include the parent_id

Answer 1

Just join with the same table twice (using aliases):

SELECT u.*
    FROM users u
    JOIN categories_users cu1 ON cu1.user_id = u.id
    JOIN categories_users cu2 ON cu2.user_id = u.id
    WHERE cu1.category_id = 1 AND cu2.category_id = 2