Limiting the next SELECT query to the last SELECT query's result in more levels without a programming loop?

I'm trying to solve this problem all afternoon but with no luck, hopefully someone will be able to help me, or at least to put me on the right track...

So, lets say I have a table like this:

+------------+-------------+
| product_开发者_运维问答id | category_id |
+------------+-------------+
|          3 |          18 |
|          3 |           6 |
|          3 |          11 |
|          4 |          18 |
|          4 |           8 |
|          4 |          12 |
|          5 |          18 |
|          5 |           7 |
|          5 |          12 |
|          6 |          11 |
|          6 |           7 |
|          6 |          10 |
|          2 |          18 |
|          2 |           5 |
|          2 |           6 |
|          2 |          10 |
|          7 |          10 |
|          7 |           7 |
|          8 |           5 |
|          8 |           8 |
|          8 |           7 |
|        ... |         ... |
+------------+-------------+

As an example: first, I'd need to get all those product_ids which are present in category_id = 7 OR category_id = 8. Then, from the given results, I'd need to limit the product_ids to those which are related to category_id = 10 OR category_id = 11. And, let's say I'd need to limit those result even deeper later on, to category_id = 5, and so on, and so on, up to a high number with always limiting the next query into the last query's result data. The reason I have to do this in one single query or a loop as I'm getting the filtering data in an array similar to this:

level_1 => 7,8
level_2 => 10,11
level_3 => 5
...
level_24 => 33

...but if there is a way to execute this in one query, I think it is understandable that I'd hope for that, rather than putting the SELECTs in a loop.

Thank you for your help in advance!

This will give you all unique product_id that satisfies your logic. You would have to construct the query dynamically in your application though.

select distinct product_id
  from your_table p1
  join your_table p2 using(product_id)
  join your_table p3 using(product_id)
 where p1.category_id in(7,8)
   and p2.category_id in(10,11)
   and p3.category_id in(5,6);

Last time I checked, MySQL didn't support INTERSECT, otherwise that would be an easier way to build the query.

This will get you the first level. You can then loop the other levels in further sub-queries based on the filtering conditions.

SELECT '1' AS level, GROUP_CONCAT(DISTINCT(product_id)) AS product_list 
FROM table WHERE category_id IN (7,8)