Mysql where clause column IN query not giving correct results

Question

Table:

From the above table, below query is not giving proper result.

SELECT * FROM `support_profile` 
WHERE `packageName` LIKE 'Platinum' AND `columnValue` IN ('50','150');

Result:

I need a query for checking the data is changed or not for the perticular packageName and columnValue?

Answer 1

Here is the internal representation of IN

Where `columnValue` In (50)     => Where (columnValue = 50)
Where `columnValue` In (50,100) => Where ( (columnValue = 50) OR (columnValue =100) )

Your query:

SELECT * FROM `support_profile` 
WHERE `packageName` LIKE 'Platinum' AND `columnValue` IN ('50','150');

is equivalent to

SELECT * FROM `support_profile` 
WHERE 
(
    (`packageName` LIKE 'Platinum')
    AND
    (
        (`columnValue` = '50')
        OR (`columnValue` = '100')
    )
);

Which will return all Platinum with columnValue 50 or 100.

if you wish to return only platinum 50 or platinum 100, you can group them. (make sure which of the latest record you want by sorting the records)

SELECT * FROM `support_profile` 
WHERE `packageName` LIKE 'Platinum' AND `columnValue` IN ('50','150')
GROUP BY `packageName`, `columnValue` ;

if you wish to know whether the package values have changed

SELECT * FROM `support_profile`, Count(distinct `columnValue`) as valueChanges
WHERE `packageName` LIKE 'Platinum' AND `columnValue` IN ('50','150')
GROUP BY `packageName` ;

you get

silver 2
gold 1
platinum 1