MySQL : interval around id column and return another one from subquery with multiple columns

Question

I would like to run a query from a table where the content is like that :

id | col1 | col2 | col3
-----------------------
1  | i_11 | i_12 | i_13
2  | i_21 | i_22 | i_23
3  | i_31 | i_32 | i_33
.. | ...  | ...  | ...

SELECT col1 FROM table WHERE id IN
(SELECT id-1, id+1 FROM table WHERE col1='xxx' AND col2='yyy' AND col3='zzz')

The aim is to get an interval [id-1, id+1] based on the id column which returns the content stored in col1 for id-1 and id+1. The subquery works but I guess I have a problem with the query itself, since I'm having an error "Operand should contain only one column". I understand it, but I don't see any other way to do it in one query ?

I'm quite sure there's a pretty easy solution but I can't figure it out for the moment, even after having carefully read other posts about multiples columns' subqueries...

Thank you for any help :-)

Answer 1

The only way I can think to do it right now is like this:

SELECT col1 
FROM table T
WHERE id BETWEEN (SELECT id FROM table WHERE col1='xxx' AND col2='yyy' AND col3='zzz') -1 
    and (SELECT id FROM table WHERE col1='xxx' AND col2='yyy' AND col3='zzz') +1

Answer 2

Your problem is that you are retrieving two values - but as a list rather than a set. The SQL optimizer can't see 1,3 as a set of two items when they are presented in a single row. There may also be a cast needed.

This should work.

SELECT col1 FROM table WHERE id in 
(
    select cast(id as int) -1 from table where col1='i_21'
    union
    select cast(id as int) +1 from table where col1='i_21'
)