MySQL query to find rows where previous and following IDs are empty

Hello,

It’s been a while since I’ve been on WebDeveloper.com, but I’ve decided to come back with a seemingly complex question about a highly customized MySQL query.

It’s best to illustrate my problem with a simplified example. Say I have a table, TestTable, with two fields, ItemID and NameID:

[code]+——–+——–+
| ItemID | NameID |
+——–+——–+
| 1 | 0 |
| 2 | 0 |
| 3 | 4 |
| 4 | 0 |
| 5 | 0 |
| 6 | 2 |
| 7 | 0 |
| 8 | 0 |
| 9 | 0 |
| 10 | 3 |
| 11 | 0 |
| 12 | 1 |
| 13 | 0 |
| 14 | 0 |
| 15 | 0 |
+——–+——–+[/code]

What I need is a query that selects all rows [i]where NameID is 0 in the current, previous, and following rows[/i]. For example, using the table above, the query should return rows 1, 8, 14, and 15.

I cams up with the query below, but it doesn’t work, and even if it did, it would take a lot of processing power because of the subquery:

[code]SELECT * FROM TestTable WHERE 0 = ALL (SELECT NameID FROM TestTable WHERE ItemID>=ItemID-1 AND ItemID<=ItemID+1)[/code]

In my actual application, I will actually be performing this query on [i]two[/i] columns in a table that might include tens of thousands of rows at a given time. I know that it might be easier to pull the table (or even part of it) into PHP and do the processing there, but this might not be plausible since it would probably need to pull thousands of rows at a time.

Any ideas? Thanks in advance.

Also in #PHP _↴