multiple find_in_set() mysql giving wrong result

Question

Here is my query :

SELECT p . * , f . * ,
CASE WHEN CAST( p.product_special_price AS DECIMAL ) > CAST( p.product_price AS DECIMAL )
THEN CAST( p.product_price AS DECIMAL )
ELSE
CASE WHEN CAST( p.product_special_price AS DECIMAL ) =0
THEN CAST( p.product_price AS DECIMAL )
ELSE CAST( p.product_special_price AS DECIMAL )
END
END AS MinPrice1
FROM crm_products p
JOIN `crm_seller_attributes` sa ON sa.seller_id = p.seller_id
LEFT JOIN `crm_product_filter` f ON p.product_id = f.product_id
AND f.seller_id = p.seller_id
WHERE p.seller_id = '63'
AND (
FIND_IN_SET( '338', f.seller_attribute_id )
OR FIND_IN_SET( '340', f.seller_attribute_id )
)
AND (
FIND_IN_SET( '737', f.options )
OR FIND_IN_SET( '736', f.options )
OR FIND_IN_SET( '749', f.options )
)
AND FIND_IN_SET( '515', p.category_ids )
AND p.product_status = 'Active'
GROUP BY p.product_id
ORDER BY p.product_id DESC
LIMIT 0 , 12

Fiddle :

http://sqlfiddle.com/#!9/2e9ee9/1

expecting : `productname` 'sjadduu' should not be there

Updated fiddle :

http://sqlfiddle.com/#!9/2e9ee9/18

Current result :

FIND_IN_SET( '338', f.seller_attribute_id ) OR FIND_IN_SET( '340', f.seller_attribute_id ) )

from this its only considering first condition FIND_IN_SET( '338', f.seller_attribute_id ) and giving result, not trying to check rest condition for OR FIND_IN_SET( '340', f.seller_attribute_id )

Expected result :

should check both :

FIND_IN_SET( '338', f.seller_attribute_id )
OR FIND_IN_SET( '340', f.seller_attribute_id )
)

We tried

FIND_IN_SET( '338', f.seller_attribute_id )
**AND** FIND_IN_SET( '340', f.seller_attribute_id )
)

but no result found giving, we are expecting the total 2 records

@SagarPanchal The second condition is considered in your resultset but the other `and` condition `AND (FIND_IN_SET( '737', f.options ) OR FIND_IN_SET( '736', f.options ) OR FIND_IN_SET( '749', f.options )` If you notice the records which has `340` as `seller_attribute_id` has only `750` as `options`. But in your and condition you do not have `750` value. Hence it is omitted in the resultset. — Viki888, Nov 28 '16 at 07:37
your query as it is will not work in mysql 5.7 can you please avoid the select.* and list only the columns that you actually need. Then please post the expected result — e4c5, Dec 02 '16 at 06:46
Sorry your data is far too broken for anything to be done with it. Your query could be just a few lines and whole lot simpler if you had a proper database design. Unfortunately the sad reality is that this is completely unusable. You would do well to abandon this and post a new question asking how to normalize your database. — e4c5, Dec 02 '16 at 09:30
What exactly do you need? You seem to say that you ONLY want crm_products records returned when they have matching rows on crm_product_filter for seller_attribute_id of both 338 and 340. Yet the test data you have put in the SQL fiddles (both the new one or the old one) have nothing that matches this (hence my earlier suggestion returned no records). Can you put up test data which can demonstrate what you want, and put an example of what you want from this test data please? — Kickstart, Dec 02 '16 at 13:15
This type of question is asked regularly on SO, so it is a duplicate question. See for example http://stackoverflow.com/questions/4047484/selecting-with-multiple-where-conditions-on-same-column — Shadow, Dec 04 '16 at 00:16

Kickstart · Accepted Answer · 2016-11-29T11:20:30.347

You appear to be using FIND_IN_SET on an integer field (that will not contain a list). If you want to check if the value of that integer field is one of a list of values then faster to use IN

SELECT p . * , 
        f . * ,
CASE 
WHEN CAST( p.product_special_price AS DECIMAL ) > CAST( p.product_price AS DECIMAL )
    THEN CAST( p.product_price AS DECIMAL )
    ELSE
        CASE WHEN CAST( p.product_special_price AS DECIMAL ) =0
            THEN CAST( p.product_price AS DECIMAL )
            ELSE CAST( p.product_special_price AS DECIMAL )
        END
    END AS MinPrice1
FROM crm_products p
JOIN `crm_seller_attributes` sa ON sa.seller_id = p.seller_id
LEFT JOIN `crm_product_filter` f ON p.product_id = f.product_id AND f.seller_id = p.seller_id
WHERE p.seller_id = '63'
AND (f.seller_attribute_id IN (338, 340))
AND (FIND_IN_SET( '737', f.options )
OR   FIND_IN_SET( '736', f.options )
OR   FIND_IN_SET( '749', f.options ))
AND  FIND_IN_SET( '515', p.category_ids )
AND  p.product_status = 'Active'
GROUP BY p.product_id
ORDER BY p.product_id DESC
LIMIT 0 , 12

EDIT

It seems you only want a product when it has ALL the selected filters (which isn't the situation in your sample data).

Couple of solutions. Simplest is to just count the distinct attributes and check it is the same as the attributes searched for:-

SELECT p . * , 
        f . * ,
CASE 
WHEN CAST( p.product_special_price AS DECIMAL ) > CAST( p.product_price AS DECIMAL )
    THEN CAST( p.product_price AS DECIMAL )
    ELSE
        CASE WHEN CAST( p.product_special_price AS DECIMAL ) =0
            THEN CAST( p.product_price AS DECIMAL )
            ELSE CAST( p.product_special_price AS DECIMAL )
        END
    END AS MinPrice1
FROM crm_products p
INNER JOIN `crm_seller_attributes` sa ON sa.seller_id = p.seller_id
INNER JOIN `crm_product_filter` f ON p.product_id = f.product_id AND f.seller_id = p.seller_id
WHERE p.seller_id = '63'
AND (f.seller_attribute_id IN (338, 340))
AND (FIND_IN_SET( '737', f.options )
OR   FIND_IN_SET( '736', f.options )
OR   FIND_IN_SET( '749', f.options ))
AND  FIND_IN_SET( '515', p.category_ids )
AND  p.product_status = 'Active'
GROUP BY p.product_id
HAVING COUNT(DISTINCT f.seller_attribute_id) = 2
ORDER BY p.product_id DESC
LIMIT 0 , 12

And alternative is to join the table once for each filter. In this case I have moved the checks on the filter to the ON clauses just to make them easier to read.

SELECT p.* , 
        f1.* ,
CASE 
WHEN CAST( p.product_special_price AS DECIMAL ) > CAST( p.product_price AS DECIMAL )
    THEN CAST( p.product_price AS DECIMAL )
    ELSE
        CASE WHEN CAST( p.product_special_price AS DECIMAL ) =0
            THEN CAST( p.product_price AS DECIMAL )
            ELSE CAST( p.product_special_price AS DECIMAL )
        END
    END AS MinPrice1
FROM crm_products p
INNER JOIN `crm_seller_attributes` sa ON sa.seller_id = p.seller_id
INNER JOIN `crm_product_filter` f1 ON p.product_id = f1.product_id AND f1.seller_id = p.seller_id AND f1.seller_attribute_id  = 338 AND (FIND_IN_SET( '737', f1.options ) OR FIND_IN_SET( '736', f1.options ) OR FIND_IN_SET( '749', f1.options ))
INNER JOIN `crm_product_filter` f2 ON p.product_id = f2.product_id AND f2.seller_id = p.seller_id AND f2.seller_attribute_id  = 340 AND (FIND_IN_SET( '737', f2.options ) OR FIND_IN_SET( '736', f2.options ) OR FIND_IN_SET( '749', f2.options ))
WHERE p.seller_id = '63'
AND  FIND_IN_SET( '515', p.category_ids )
AND  p.product_status = 'Active'
GROUP BY p.product_id
ORDER BY p.product_id DESC
LIMIT 0 , 12

In either case you have an issue that you are returning all the columns from the filter table but using GROUP BY p.product_id. This will result in one of the rows of matching filters being returned, but which one is not defined.

no change, still getting `1579 | 63 | simple-custom | sjadduu `, which do not having `f.options` value — SagarPPanchal, Nov 29 '16 at 05:27
@SagarPanchal - it does have the f.options. Seller id 63 product id 1579 has 2 matching records on the crm_product_filter table (filter id 407 and 409). The first of these (407) has an options field of 736, which is one that you require. — Kickstart, Nov 29 '16 at 09:38
@SagarPanchal - couple of solutions added to what you appear to require, — Kickstart, Nov 29 '16 at 11:20
@kichstart, no records founds :( , actually properly two records are there, as in example. tried your both queries, but didn't work — SagarPPanchal, Nov 29 '16 at 12:49
@SagarPanchal In your test data there are NO records that match your requirements, if I understand them. Product 1406 only has a single record on attributes for seller_attribute_id of 338 and not 340. 1415 only has a single record on attributes for seller_attribute_id of 338 and not 340. 1579 has 2 records, one for each seller_attribute_id of 338 and 340 but one of those has an option of 750 which is thus excluded. So either all 3 match, or none match depending on whether you need both seller_attribute_id or just 1. — Kickstart, Nov 29 '16 at 12:54

Bernd Buffen · Answer 2 · 2016-11-28T07:52:04.930

So, you must modify the FIND_IN_SET. The fieldlist is the second argument

EXAMPLE

SELECT p . * , f . * ,
CASE WHEN CAST( p.product_special_price AS DECIMAL ) > CAST( p.product_price AS DECIMAL )
THEN CAST( p.product_price AS DECIMAL )
ELSE
CASE WHEN CAST( p.product_special_price AS DECIMAL ) =0
THEN CAST( p.product_price AS DECIMAL )
ELSE CAST( p.product_special_price AS DECIMAL )
END
END AS MinPrice1
FROM crm_products p
JOIN `crm_seller_attributes` sa ON sa.seller_id = p.seller_id
LEFT JOIN `crm_product_filter` f ON p.product_id = f.product_id AND f.seller_id = p.seller_id

WHERE p.seller_id = '63'
AND FIND_IN_SET( CAST(f.seller_attribute_id as CHAR),'338,340' )
AND (
    FIND_IN_SET( '736', CAST(f.options as CHAR)) OR
    FIND_IN_SET( '737', CAST(f.options as CHAR)) OR
    FIND_IN_SET( '749', CAST(f.options as CHAR))
)
AND FIND_IN_SET( '515', CAST(p.category_ids as CHAR))
AND p.product_status = 'Active'
GROUP BY p.product_id
ORDER BY p.product_id DESC
LIMIT 0 , 12;

result

+------------+-----------+---------------+--------------------------------+---------------+-----------------------+--------------------------------+-------------------+-------------+-------------------------------------+-------------+-----------------+-----------------+----------------+----------------------------+----------------------------+---------------------+----------------+---------------+----------------+---------------+--------------------+-----------+-----------+------------+---------------------+---------+-----------+
| product_id | seller_id | product_type  | product_name                   | product_price | product_special_price | product_sku                    | category_ids      | product_upc | product_image                       | product_qty | product_min_qty | product_max_qty | product_weight | short_description          | long_description           | created_at          | product_status | product_width | product_height | product_depth | product_sort_order | filter_id | seller_id | product_id | seller_attribute_id | options | MinPrice1 |
+------------+-----------+---------------+--------------------------------+---------------+-----------------------+--------------------------------+-------------------+-------------+-------------------------------------+-------------+-----------------+-----------------+----------------+----------------------------+----------------------------+---------------------+----------------+---------------+----------------+---------------+--------------------+-----------+-----------+------------+---------------------+---------+-----------+
|       1579 |        63 | simple-custom | sjadduu                        |           500 |                    15 | erettrtr                       | 86,515,87,515,620 |             | NULL                                | 100         | 1               |              10 | NULL           | NULL                       | NULL                       | 2016-11-25 11:36:53 | Active         | NULL          | NULL           | NULL          |               NULL |       407 |        63 |       1579 |                 338 | 736     |        15 |
|       1415 |        63 | simple-custom | Double Cheesy Margherita Pizza |           170 |                     0 | Double Cheesy Margherita Pizza | 515,87,515        |             | 1479557588_235.png                  | 100         | 1               |             100 |                |                            |                            | 2016-11-19 12:25:51 | Active         |               |                |               |                  0 |       289 |        63 |       1415 |                 338 | 737,736 |       170 |
|       1406 |        63 | simple-custom | Cheese Burst Pizza             |           250 |                     0 | 123456                         | 515,87,515        |             | 1479116616_pizza_trad_pepperoni.png | 97          | 1               |             100 |                | <p>chesse burst pizza</p>
 | <p>chesse burst pizza</p>
 | 2016-11-21 06:09:41 | Active         |               |                |               |                  0 |       256 |        63 |       1406 |                 338 | 737,736 |       250 |
+------------+-----------+---------------+--------------------------------+---------------+-----------------------+--------------------------------+-------------------+-------------+-------------------------------------+-------------+-----------------+-----------------+----------------+----------------------------+----------------------------+---------------------+----------------+---------------+----------------+---------------+--------------------+-----------+-----------+------------+---------------------+---------+-----------+
3 rows in set (0,01 sec)

not expecting `1579 | 63 | simple-custom | sjadduu ` product, its only having seller_attribute_id but not having option_id as in requested query — SagarPPanchal, Nov 28 '16 at 08:17
@Sagar Panchal - Why ? 1579 is Active, have category = 515, seller_attribute_id = 338 and seller_id = 63 — Bernd Buffen, Nov 28 '16 at 08:24
that prodcut is not having matching attributes, its only having one seller attibute it, not both `338` and `340` — SagarPPanchal, Nov 28 '16 at 08:27
@SagarPanchal - But none of the rows have BOTH 338 and 340 option rows, so if you want only rows with them both then no rows should be returned. If that is what you want then the solution is easy (just add _HAVING COUNT(DISTINCT f.seller_attribute_id) = 2_ ) — Kickstart, Nov 29 '16 at 10:30
@Kickstart, you are right my friend, but this is example of filter, in which ex. filter those product which is only having `red` `colored` with `size` of `small`, so `id` of red and size both should be there, and in our case, both is assigned for that two products as you know, rest third product doesnt have 2nd matching attribute — SagarPPanchal, Nov 29 '16 at 11:04
@SagarPanchal Can you modify your sample data to show the actual issue please. — Kickstart, Nov 29 '16 at 11:13

multiple find_in_set() mysql giving wrong result

2 Answers2