Here is the situation:
Lets assume we have a very simple containing products_id and options_id, each pair is unique, but of course 1 products_id can associate with multiple options_id (many to many relationship)
products_id - options_id
1 - 2
1 - 4
1 - 5
2 - 2
2 - 1
Now I want to get all products id that contain options 2 AND 4, so I write a query like this:
SELECT products_id from TESTTABLE WHERE products_id IN(SELECT products_id from TESTTABLE WHERE options_id = 2) AND options_id = 4
The problem with this is, if I want to filter by say 4 or options_id I will have 4 SELECT IN statements, and my query can run for many seconds.
I wonder which is the best approach to solve this problem? Maybe a mix of php (Im using php) and mysql? Or maybe there is a better sql query for this situation?
Thank you so much!