Please suggest me how to improve this search query

MySQL & MariaDB Other MySQL® Questions
1

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)
Example:
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!

2

Hello,

Ok I want to make sure I am understanding you correctly; my brain is a little fried from homework right now so bear with me. You want to retrieve all product_id’s whose corresponding option_id is either 2 or 4 correct? So given the example entries you provided the following rows would match:

1 - 2
1 - 4
2 - 2

So the resulting product_id’s would be 1, 1, 2 right? Couldn’t you just do:

SELECT product_id
FROM TESTTABLE
WHERE option_id = 2 or option_id=4

If you want you could change the where clause to “WHERE option_id IN (2,4)”. Or am I missing something?

3

Hi malonso,

I wanted AND, not OR. So I want products that have both options (remember this table is a many to many relationship)

Anyhow, I solved it by using mysql along with php (I query all the products that have either options, then I put them in array, and then I use array_intersect to get the ones that have both options)

Regards

4

Just a little bit longer.

SELECT products_id
FROM testtable
WHERE options_id in B[/B]
group by products_id
having count(*) = 2

In WHERE clause you list all values you need
In having clause you put the number of values in the list.

That’s all.

Ah, yes, BOTH columns should be in primary key.