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?
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.
