SELECT N rows before and after the row matching the condition?

Question

The behaviour I want to replicate is like grep with -A and -B flags . eg grep -A 2 -B 2 "hello" myfile.txt will give me all the lines which have "hello" in them, but also 2 lines before and 2 lines after it. Lets assume this table schema :

+--------+-------------------------+
| id     |    message              |
+--------+-------------------------+
| 1      | One tow three           |
| 2      | No error in this        |
| 3      | My testing message      |
| 4      | php module test         |
| 5      | hello world             |
| 6      | team spirit             |
| 7      | puzzle game             |
| 8      | social game             |
| 9      | stackoverflow           |
|10      | stackexchange           |
+------------+---------------------+

Now a query like : Select * from theTable where message like '%hello%' will result in :

5 | hello world

How can I put another parameter "N" which selects N rows before, and N rows after the matched record i.e. for N = 2, the result should be :

    | 3      | My testing message      |
    | 4      | php module test         |
    | 5      | hello world             |
    | 6      | team spirit             |
    | 7      | puzzle game             |

• For simplicity assume 'like %TERM%' matches only 1 row .
• Here the result is supposed to be sorted on auto-increment id field.

Answer 1

Right, this works for me:

SELECT child.*
FROM stack as child,
(SELECT idstack FROM stack WHERE message LIKE '%hello%') as parent
WHERE child.idstack BETWEEN parent.idstack-2 AND parent.idstack+2;