MYSQL - Possibly FIND_IN_SET

Question

I'm really struggling to figure this query out:

names	zone	zones	zone_active
bob	1	1,2	yes
bill	0	3	yes
james	1	1,2	yes
fred	1	1,2	no
barry	1	4	yes

Im selecting zones '1,2' and zone_active='yes'

But it's returning all rows except Bill and Barry its seems to be ignoring the zone_active part

SELECT p.names, n.zone, n.zones, n.zone_active
FROM names as n
JOIN people as p ON p.names=n.names
WHERE zone IN ('1,2') AND zone_active='yes'

It should only return - bob, james

any ideas?

Answer 1

IN() does not treat a string as a list of discrete values just because the string contains commas. The value '1,2' is a single value, a string. So the operand will be compared to one value.

I assume zone is an integer (though you have not shown your table's data types, so that might be a wrong assumption). If you compare an integer to a string that has leading digits like '1,2', the initial digits are converted to an integer, and then compared.

So if zone is an integer column, then this:

WHERE zone IN ('1,2')

Is equivalent to this:

WHERE zone = 1 

This does not match the row for bill because zone is 0 and the integer conversion for '1,2' is 1. So 0 = '1,2' is false.

The query should match the row for barry because 1 = '1,2' is true. I wonder if you made some mistake in your question.

---

Re your comment:

I want it to check if 1 or 2 is in the zone field

That would be:

WHERE zone IN (1,2) AND zone_active='yes'

But not:

WHERE zone IN ('1,2') AND zone_active='yes'

If you're trying to use query parameters, you need a separate parameter for each value:

WHERE zone IN (?, ?) AND zone_active='yes'

Because each parameter will be interpreted as one value. You can't use a single parameter for a list of values. If you try to use a parameter value of '1,2' that will become one string value, and that'll behave as I described above.

Some people try to fake this using FIND_IN_SET() but I would not recommend that. It will ruin any chance of optimizing the query with indexes.