Dynamic Query to Select Multiple Entries from a Relational Record Table

Let's say I have a table which stores the relation between products and their categories:

p_id | c_id
-----+-----
  1  |  1
  1  |  2
  2  |  1
  2  |  2
  2  |  3
  3  |  2

As you can see, a product might have multiple categories. How can I search for products that have categories 1 and 2 assigned? The closest I can think of is using JOIN:

SELECT a.p_id
 FROM rel_table a
 JOIN rel table b
   ON a.p_id=b.p_id AND b.c_id=2
WHERE a.c_id=1

While this achieves what I want, it is not practical because my query will be dynamic. If I have to select products with 3 categories, this requires a difficult change in the query.

Is there a cleaner and more clever way to achieve this? I imagine something that selects first set, then refines with another category for the amount of levels needed.

Solution

You should use IN or Between for such things. You can dynamically create the values you put in the IN/BETWEEN

SELECT a.p_id
FROM rel_table a
WHERE a.c_id IN (1,2,3)
group by a.p_id
having count(1) = 3
order by a.p_id asc

SELECT a.p_id
FROM rel_table a
WHERE a.c_id between 1 and 3
group by a.p_id
having count(1) = 3
order by a.p_id asc