Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004
    Posts
    4

    Unanswered: Howto select the next free date?

    I have a table that has an int (kind of a foreign key) and a date as primary key. Simplified it looks like this:
    Code:
    memberpages:
    ----------------------------
    | member_id | page_created |
    ----------------------------
    | 1         | 2004-03-14   |
    | 1         | 2004-03-13   |
    | 1         | 2004-03-11   |
    ----------------------------
    Ok - i'm using mySQL 3.23.49 (which doesn't support sub selects) and i try to do a query, that selects from a defined date on the next unused date in the past.

    So if 2004-03-14 is the defined date, the query should output 2004-03-12.

    Anyone has an idea how to do this? I've already tried to join the table with itself but i couldn't get an useful output from that. The ugly way would be to do a loop in PHP until it selects a count(*) = 0. But i'd prefer to avoid that.

    Any help is appreciated. Thanks,
    tREXX

  2. #2
    Join Date
    May 2004
    Location
    Barcelona, Spain
    Posts
    54
    Hi!

    Can't try this myself, but might help you:

    select a.page_created defined, a.page_created - 1 unused
    from memberpages a, memberpages b
    where a.page_created > b.page_created
    and page_created = 'your date'
    group by 1
    having a.page_created-1 > max(b.page_created)

    regards

Posting Permissions

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