MySQL query to find rows where previous and following IDs are empty
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:
What I need is a query that selects all rows where NameID is 0 in the current, previous, and following rows. For example, using the table above, the query should return rows 1, 8, 14, and 15.
| 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 |
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:
In my actual application, I will actually be performing this query on two 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.
SELECT * FROM TestTable WHERE 0 = ALL (SELECT NameID FROM TestTable WHERE ItemID>=ItemID-1 AND ItemID<=ItemID+1)
Any ideas? Thanks in advance.
I found a way around my problem by using a table to keep track of certain "sections" to be filled to bring fewer rows into PHP for analyzing
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)