www.webdeveloper.com
Results 1 to 2 of 2

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

  1. #1
    Join Date
    May 2006
    Location
    North of the South Pole
    Posts
    590

    Arrow 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      |
    +--------+--------+
    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.

    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)
    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.

    Any ideas? Thanks in advance.
    "I have no special talent. I am only passionately curious." Albert Einstein

  2. #2
    Join Date
    May 2006
    Location
    North of the South Pole
    Posts
    590
    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
    "I have no special talent. I am only passionately curious." Albert Einstein

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles