MySQL search with the word distance in neighboring records

Question

I do a text search on keywords through static entries in a MySQL database. Is it possible to build a query that finds the first keyword in the record and the second keyword in the neighboring record? Consider the following sample data.

------------------------------------------------------
| id | textstrings                                   |
------------------------------------------------------
|  1 | Every good boy does fine.                     |
|  2 | The quick brown fox jumped over the lazy dog. |
|  3 | I will not eat green eggs and ham.            |
|  4 | There is no time like the present.            |
|  5 | Envy is an ugly shade of green.               |
------------------------------------------------------

The search for terms green brownshould return records 2 and 3, since they are neighboring records, but should not contain record 5, since it is not a neighbor of record 3.

I know that I can execute a query for one of the words and complete this by processing the result set, but I would like to know if it is possible to build it in the query.

There is a FULLTEXT index in this field.

Answer 1

, , (green ), , , (brown ).

SELECT , id, WHERE.

SELECT LEAST(t1.id, t2.id) AS id1, GREATEST(t1.id, t2.id) AS id2
FROM
(
    SELECT id
    FROM table
    WHERE textstrings LIKE '%green%'
) t1
INNER JOIN
(
    SELECT id
    FROM table
    WHERE textstrings LIKE '%brown%'
) t2
ON t1.id = t2.id - 1 OR t1.id = t2.id + 1
GROUP BY LEAST(t1.id, t2.id), GREATEST(t1.id, t2.id)

.

SQLFiddle