2

I have an events table with a field called breaks. This is populated with data in a comma separated format, i.e. 1,2,3 or 1 or 1,4,5 - the same format that MySQL's IN command uses.

I'd then like to run a query - on the slots table - to return all rows apart from those specified in events.breaks.

The query, theoretically, should be something like this:

SELECT
    `slots`.`id` AS id,
    RIGHT(`slots`.`time`, 8) AS `time`
FROM
    `slots`, `event`
WHERE
    `slots`.`id` NOT IN (`event`.`breaks`)

But that doesn't appear to work - if event.breaks is 4,5,7, the only row from the slots table that doesn't return is 4!

SQLFiddle here: http://sqlfiddle.com/#!2/913fe/1/0

turbonerd
  • 1,234
  • 4
  • 27
  • 63

3 Answers3

3

You're passing a single field to the NOT IN () clause, not a subexpression. Think of it like this

(1, 2, 3)

is roughly the same as

SELECT 1
UNION
SELECT 2
UNION
SELECT 3;

as a subexpression. What you're doing instead is

('4,5,7')

which is roughly equivalent to

SELECT '4,5,7';

which in turn MySQL probably converted to a number for the comparison and the result is

NOT IN (4)

What you're actually trying to do isn't really supposed to be done like that. It'd be better if you added an AxB relation table so you can select several rows with the IDs you don't want.

Magnus
  • 980
  • 4
  • 10
  • Aye, using FIND_IN_SET works, but it'll be cleaner to have a separate table for that with distinct rows for each of the entries you don't want - it'll probably be faster too if your data ends up growing ;). – Magnus Oct 16 '13 at 15:27
2

Give this a try:

SELECT slots.id AS id, RIGHT(slots.time, 8) time
FROM slots, event
WHERE FIND_IN_SET(slots.id, event.breaks) = 0

This is how the FIND_IN_SET(str,strlist) function works:

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters. [...] Returns 0 if str is not in strlist or if strlist is the empty string.

Also note that IN (val1, val2, val3) is NOT the same as IN (val4) where val4 is a commma-separated string. The IN clause will compare by equality.

Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
  • 1
    Perfect - but I originally got a `FIND_IN_SET function does not exist` error because of the space between `FIND_IN_SET` and `(`! :) Just in case anyone else is looking for the answer. Thanks Mosty. – turbonerd Oct 16 '13 at 15:23
  • Welcome. Weirdest thing the space issue :) – Mosty Mostacho Oct 16 '13 at 15:25
0

you may need a subselect to return the split string

... NOT IN (SELECT your_split_fnc(`event`.`breaks`) FROM `events`)

See answers here for a way to split strings in MySQL Can Mysql Split a column?

instr() MySQL function could be of help also

... INSTR(event.breaks,id) = 0

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_instr

Community
  • 1
  • 1
LMC
  • 10,453
  • 2
  • 27
  • 52