CODEWITHSUNDEEP
mysqlwhere-in
fsasvari
fsasvari

Reputation: 1951

MySQL multiple WHERE IN on same column with AND operator

Need some help with MySQL.

I'm building web shop and I'm having problems with filtering search results with multiple WHERE IN clauses on same column.

I have tables shopitem, filter, filter_option and shopitem_option.

shopitem (Items with name, description, price etc.)

id
title
price

filter (Main filter categories like color, brand, gender, size etc.)

id
name

filter_option (filter options like sizes (S, M, L), colors (black, white) etc.)

id
name
filter_id (filter.id)

shopitem_option (connection between items and options)

id
shopitem_id (shopitem.id)
filter_id (filter.id)
filter_option_id (filter_option.id)

I have items like various Nike Air shoes with different size, color, gender etc...

So in shopitem_option table I have following:

id  |  shopitem_id  |  filter_id  |  filter_option_id
-----------------------------------------------------
1   |  Nike Air     |  Color      |  black
2   |  Nike Air     |  Color      |  white
3   |  Nike Air     |  Size       |  40
4   |  Nike Air     |  Size       |  41
5   |  Nike Air     |  Size       |  42
6   |  Nike Air     |  Gender     |  man

When I'm creating MySQL query it need to be something like this (in pure SQL query instead of 'black', 'men' etc. there are IDs of filter_option.id field)

SELECT shopitem.*
FROM shopitem
LEFT JOIN shopitem_option.shopitem_id = shopitem.id
WHERE
    shopitem_option.filter_option_id IN (black) AND 
    shopitem_option.filter_option_id IN (41,42) AND 
    shopitem_option.filter_option_id IN (man)

But this does not work. If I search only for size 40, or only for color black, it will return all shoes sizes 40 or all shoes with black color. But if I combine filter to search items with color black and size 40 it returns empty query results.

So, basically user can search for shoes WHERE colors (black OR white) AND size (41 OR 42) AND gender (male). How can i accomplish that ?

Upvotes: 2

Views: 2269

Answers (2)

Arun Krish
Arun Krish

Reputation: 2153

Use it like this

SELECT si.*, GROUP_CONCAT(so.filter_option_id) AS filter_options
FROM shopitem si
LEFT JOIN shopitem_option so ON(so.shopitem_id = si.id)
HAVING
FIND_IN_SET('black', filter_options)
AND  FIND_IN_SET('41', filter_options)
AND FIND_IN_SET('man', filter_options)
GROUP BY si.id

For counting the number of si.id from the result you can use like this with subquery

SELECT COUNT(res.id) FROM
  (SELECT si.*, GROUP_CONCAT(so.filter_option_id) AS filter_options
   FROM shopitem si
   LEFT JOIN shopitem_option so ON(so.shopitem_id = si.id)
   HAVING
   FIND_IN_SET('black', filter_options)
   AND  FIND_IN_SET('41', filter_options)
   AND FIND_IN_SET('man', filter_options)
   GROUP BY si.id) 
 AS res

Upvotes: 1

rMX
rMX

Reputation: 1090

IN ... AND IN ... can't work here, because you tell database to select shopitem_option where filter_option_id should be black and 41 for one same record. Use EXISTS like this:

SELECT *
  FROM shopitem i
 WHERE EXISTS (SELECT 1 FROM shopitem_option o WHERE i.id = o.shopitem_id AND o.filter_option_id IN (black))
   AND EXISTS (SELECT 1 FROM shopitem_option o WHERE i.id = o.shopitem_id AND o.filter_option_id IN (41, 42))
   AND EXISTS (SELECT 1 FROM shopitem_option o WHERE i.id = o.shopitem_id AND o.filter_option_id IN (man))

Upvotes: 3

Related Questions