Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2012
    Posts
    10

    Unanswered: Joining same table to pick up field in the next record

    I'm baffled!

    I thought this would work:

    SELECT UserID as UI,
    UserName as Uname,
    UserDate as Udate FROM User_table as U1
    INNDR JOIN (SELECT Udate as Udate2 FROM User_table as U2 LIMIT 1)
    WHERE U1.UserID < U2.UserID;

    I wanted the record to print and the NEXT UserDate to print on the same line.

    But it says 'No object Returned'. And I did load the database beforehand and verified the contents were there.

    Any ideas?

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try this instead:
    Code:
    SELECT this.UserID AS UI
    ,  this.UserName   AS Uname
    ,  this.UserDate   AS Udate
       FROM User_table AS this
       INNER JOIN User_table AS next
          ON next.User_id = 
             (SELECT Min(z.UserID
                FROM User_Table AS z
                WHERE  this.UserID < z.UserID);
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2012
    Posts
    10
    Well I tried:

    SELECT U1.UserID as UI,
    U1.UserName as Uname,
    U1.UserDate as Udate,
    FROM User_table as U1
    INNER JOIN User_table as U2 ON U2.UserID =
    (SELECT U3.UserDate as Udate2 FROM User_table as U3
    WHERE U1.UserID < U3.UserID);

    But still 'no object found'. But I don't understand why the three tables. Should it not be just two, both the same table?

    Basicly I have the table with

    ('10000', 'Mack', STR_TO_DATE('01/01/2014 00:00:00', '%c/%e/%Y %H:%i:%s')),
    ('20000', 'Sam', STR_TO_DATE('01/02/2014 00:00:00', '%c/%e/%Y %H:%i:%s')),
    ('30000', 'Bob', STR_TO_DATE('01/03/2014 00:00:00', '%c/%e/%Y %H:%i:%s')),
    ('40000', 'David', STR_TO_DATE('01/04/2014 00:00:00', '%c/%e/%Y %H:%i:%s')),
    ('50000', 'Kelly', STR_TO_DATE('01/05/2014 00:00:00', '%c/%e/%Y %H:%i:%s'));

    And I want the output to show like:
    10000 Mack 01/01/2014 00:00:00 01/02/2014 00:00:00 where the second date/time is from the second record and
    20000 Sam 01/02/2014 00:00:00 01/03/2014 00:00:00 where the second date/time is from the third record and so on.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    To confirm; what makes the "first row" the first i.e. what orders your data set?
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2012
    Posts
    10
    The table uses the UserID that normaly is autoincreated (the one with 10000, 20000, etc.) above.

    CREATE TABLE IF NOT EXISTS User_table (
    UserID int (18) NOT NULL AUTO_INCREMENT,
    UserName CHAR (20) null,
    UserDate DATETIME DEFAULT NULL,
    PRIMARY KEY (UserID)
    ) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=1 ;

    I just loaded it with 10000, 20000, etc...

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I missed the part about showing the "next" date from your original requirement. Try:
    Code:
    SELECT this.UserID AS UI
    ,  this.UserName   AS Uname
    ,  this.UserDate   AS Udate
    ,  next.UserDate   AS NextDate
       FROM User_table AS this
       INNER JOIN User_table AS next
          ON next.User_id = 
             (SELECT Min(z.UserID)
                FROM User_Table AS z
                WHERE  this.UserID < z.UserID);
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    Code:
    SELECT UserID
         , UserName
         , UserDate
         , (SELECT UserDate
             FROM  User_table AS p
             WHERE p.UserID > r.UserID
             ORDER BY
                   UserID ASC
             LIMIT 1
           ) AS NextDate
     FROM  User_table AS r
    ;

Posting Permissions

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