SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Traversal methods

    Hello everyone.

    Thanks all in advance for any help and suggestions.

    This is table «forums» in MySQL:

    Code:
    DROP TABLE IF EXISTS `forums`;
    CREATE TABLE `forums` (
      `id` int(10) NOT NULL AUTO_INCREMENT,
      `connected` int(11) DEFAULT NULL,
      `datum` datetime DEFAULT NULL,
      `datum_update` datetime DEFAULT NULL,
      `title` varchar(255) DEFAULT NULL,
      `author` varchar(255) DEFAULT NULL,
      `message` varchar(255) DEFAULT NULL,
      `last_replies` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=396 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of forums
    -- ----------------------------
    INSERT INTO `forums` VALUES ('392', '0', '2012-08-16 11:19:16', '2012-08-16 11:08:00', 'help me', 'Sandra', 'hello my friend', 'Sandra');
    INSERT INTO `forums` VALUES ('394', '392', '2012-08-24 12:15:27', '2012-08-24 00:08:00', 'help me', 'admin', 'hi there', 'admin');
    INSERT INTO `forums` VALUES ('395', '392', '2013-01-24 13:17:27', '2013-01-24 01:17:00', 'help me', 'Sammy', 'regards', 'Sammy');
    You can't have this output?
    Code:
    +-----+-----------+--------------------------+----------------------+---------+--------+----------+--------------+
    | ID  | connected | datum                    | datum_update         | title   | author | message  | last_replies |
    +-----+-----------+--------------------------+----------------------+---------+--------+----------+--------------+
    | 395 | 392       | 2012-08-16 11:19:16      | 2013-01-24 01:17:00  | help me | Sandra | regards  | Sammy        |
    +-----+-----------+--------------------------+----------------------+---------+--------+----------+--------------+
    Because this query has this output:
    Code:
    mysql> SELECT
    	A.ID,
    	A.connected,
    	B.id,
    	B.connected,
    	A.datum,
    	B.datum_update,
    	A.title,
    	A.author,
    	B.message,
    	B.last_replies
    FROM
    	forums a
    JOIN forums b ON a.id = b.connected;
    +-----+-----------+-----+-----------+---------------------+---------------------+---------+--------+----------+--------------+
    | ID  | connected | id  | connected | datum               | datum_update        | title   | author | message  | last_replies |
    +-----+-----------+-----+-----------+---------------------+---------------------+---------+--------+----------+--------------+
    | 392 |         0 | 394 |       392 | 2012-08-16 11:19:16 | 2012-08-24 00:08:00 | help me | Sandra | hi there | admin        |
    | 392 |         0 | 395 |       392 | 2012-08-16 11:19:16 | 2013-01-24 01:17:00 | help me | Sandra | regards  | Sammy        |
    +-----+-----------+-----+-----------+---------------------+---------------------+---------+--------+----------+--------------+
    2 rows in set
    
    mysql>

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select A.ID,
           A.connected,
           B.id,
           B.connected,
           A.datum,
           B.datum_update,
           A.title,
           A.author,
           B.message,
           B.last_replies
      from forums a
      join forums b
        on a.id = b.connected
      join (select connected,
                   max(datum_update) as max_date
              from forums
             group
                by connected) dt
        on (b.connected,b.datum_updated) = (dt.connected,dt.max_date)

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    You can't have this output?
    of course you can

    Code:
    SELECT B.id
         , B.connected
         , A.datum
         , B.datum_update
         , A.title
         , A.author
         , B.message
         , B.last_replies
      FROM forums AS A
    INNER
      JOIN forums AS B 
        ON B.connected = A.id
       AND B.id = 395
    piece of cake
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by swampBoogie View Post
    Code:
    select A.ID,
           A.connected,
           B.id,
           B.connected,
           A.datum,
           B.datum_update,
           A.title,
           A.author,
           B.message,
           B.last_replies
      from forums a
      join forums b
        on a.id = b.connected
      join (select connected,
                   max(datum_update) as max_date
              from forums
             group
                by connected) dt
        on (b.connected,b.datum_updated) = (dt.connected,dt.max_date)
    I have this error with your suggestion:
    Code:
    mysql> select A.ID,
           A.connected,
           B.id,
           B.connected,
           A.datum,
           B.datum_update,
           A.title,
           A.author,
           B.message,
           B.last_replies
      from forums a
      join forums b
        on a.id = b.connected
      join (select connected,
                   max(datum_update) as max_date
              from forums
             group
                by connected) dt
        on (b.connected,b.datum_updated) = (dt.connected,dt.max_date);
    1054 - Unknown column 'b.datum_updated' in 'on clause'
    mysql>

  5. #5
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    of course you can

    Code:
    SELECT B.id
         , B.connected
         , A.datum
         , B.datum_update
         , A.title
         , A.author
         , B.message
         , B.last_replies
      FROM forums AS A
    INNER
      JOIN forums AS B 
        ON B.connected = A.id
       AND B.id = 395
    piece of cake
    Of course

    Your version is good, but when I have other id -last replies for thread- to show?

  6. #6
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    A spelling mistake on my part, it should be

    Code:
    on (b.connected,b.datum_update) = (dt.connected,dt.max_date)

  7. #7
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by swampBoogie View Post
    A spelling mistake on my part, it should be

    Code:
    on (b.connected,b.datum_update) = (dt.connected,dt.max_date)
    Oh, jolly good. Thanks!
    Code:
    mysql> SELECT
    	A.ID,
    	A.connected,
    	B.id,
    	B.connected,
    	A.datum,
    	B.datum_update,
    	A.title,
    	A.author,
    	B.message,
    	B.last_replies
    FROM
    	forums a
    JOIN forums b ON a.id = b.connected
    JOIN (
    	SELECT
    		connected,
    		max(datum_update) AS max_date
    	FROM
    		forums
    	GROUP BY
    		connected
    ) dt ON (b.connected, b.datum_update) = (dt.connected, dt.max_date);
    +-----+-----------+-----+-----------+---------------------+---------------------+---------+--------+---------+--------------+
    | ID  | connected | id  | connected | datum               | datum_update        | title   | author | message | last_replies |
    +-----+-----------+-----+-----------+---------------------+---------------------+---------+--------+---------+--------------+
    | 392 |         0 | 395 |       392 | 2012-08-16 11:19:16 | 2013-01-24 01:17:00 | help me | Sandra | regards | Sammy        |
    +-----+-----------+-----+-----------+---------------------+---------------------+---------+--------+---------+--------------+
    1 row in set
    
    mysql>

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    last replies for thread
    swampboogie figured it out, but then, swampboogie is my sql hero and he can do anything

    for the rest of us, it would really help if you said "last replies for thread" in the very first post
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    swampboogie figured it out, but then, swampboogie is my sql hero and he can do anything

    for the rest of us, it would really help if you said "last replies for thread" in the very first post
    Of course boss

  10. #10
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    last replies for thread

    Hello everyone.

    Thanks all in advance for any help and suggestions.

    This query is a problem: in output not are visible the new threads without replies.

    Please check this:
    Code:
    mysql> SELECT
    	A.ID,
    	A.connected,
    	B.id,
    	B.connected,
    	A.datum,
    	B.datum_update,
    	A.title,
    	A.author,
    	B.message,
    	B.last_replies
    FROM
    	forums a
    JOIN forums b ON a.id = b.connected
    JOIN (
    	SELECT
    		connected,
    		max(datum_update) AS max_date
    	FROM
    		forums
    	GROUP BY
    		connected
    ) dt ON (b.connected, b.datum_update) = (dt.connected, dt.max_date);
    +-----+-----------+-----+-----------+---------------------+---------------------+---------+--------+---------+--------------+
    | ID  | connected | id  | connected | datum               | datum_update        | title   | author | message | last_replies |
    +-----+-----------+-----+-----------+---------------------+---------------------+---------+--------+---------+--------------+
    | 392 |         0 | 395 |       392 | 2012-08-16 11:19:16 | 2013-01-24 01:17:00 | help me | Sandra | regards | Sammy        |
    +-----+-----------+-----+-----------+---------------------+---------------------+---------+--------+---------+--------------+
    1 row in set
    
    mysql> 
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for `forums`
    -- ----------------------------
    DROP TABLE IF EXISTS `forums`;
    CREATE TABLE `forums` (
      `id` int(10) NOT NULL AUTO_INCREMENT,
      `connected` int(11) DEFAULT NULL,
      `datum` datetime DEFAULT NULL,
      `datum_update` datetime DEFAULT NULL,
      `title` varchar(255) DEFAULT NULL,
      `author` varchar(255) DEFAULT NULL,
      `message` varchar(255) DEFAULT NULL,
      `last_replies` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=398 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of forums
    -- ----------------------------
    INSERT INTO `forums` VALUES ('392', '0', '2012-08-16 11:19:16', '2012-08-16 11:08:00', 'help me', 'Sandra', 'hello my friend', 'Sandra');
    INSERT INTO `forums` VALUES ('394', '392', '2012-08-24 12:15:27', '2012-08-24 00:08:00', 'help me', 'admin', 'hi there', 'admin');
    INSERT INTO `forums` VALUES ('395', '392', '2013-01-24 13:17:27', '2013-01-24 01:17:00', 'help me', 'Sammy', 'regards', 'Sammy');
    INSERT INTO `forums` VALUES ('396', '0', '2013-02-16 21:28:42', '2013-02-16 21:28:49', 'new thread', 'Rudy', 'this is new thread', 'Rudy');
    INSERT INTO `forums` VALUES ('397', '0', '2013-02-18 21:35:59', '2013-02-18 21:36:04', 'post new thread', 'swampBoogie', 'this is my new thread', 'swampBoogie');

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    in output not are visible the new threads without replies.
    use LEFT OUTER JOINs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    use LEFT OUTER JOINs
    Thank you, but...
    Code:
    mysql> SELECT
    	A.ID,
    	A.connected,
    	B.id,
    	B.connected,
    	A.datum,
    	B.datum_update,
    	A.title,
    	A.author,
    	B.message,
    	B.last_replies
    FROM
    	forums a
    LEFT OUTER JOIN forums b ON a.id = b.connected
    LEFT OUTER JOIN (
    	SELECT
    		connected,
    		max(datum_update) AS max_date
    	FROM
    		forums
    	GROUP BY
    		connected
    ) dt ON (b.connected, b.datum_update) = (dt.connected, dt.max_date);
    +-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+----------+--------------+
    | ID  | connected | id   | connected | datum               | datum_update        | title           | author      | message  | last_replies |
    +-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+----------+--------------+
    | 392 |         0 |  394 |       392 | 2012-08-16 11:19:16 | 2012-08-24 00:08:00 | help me         | Sandra      | hi there | admin        |
    | 392 |         0 |  395 |       392 | 2012-08-16 11:19:16 | 2013-01-24 01:17:00 | help me         | Sandra      | regards  | Sammy        |
    | 394 |       392 | NULL | NULL      | 2012-08-24 12:15:27 | NULL                | help me         | admin       | NULL     | NULL         |
    | 395 |       392 | NULL | NULL      | 2013-01-24 13:17:27 | NULL                | help me         | Sammy       | NULL     | NULL         |
    | 396 |         0 | NULL | NULL      | 2013-02-16 21:28:42 | NULL                | new thread      | Rudy        | NULL     | NULL         |
    | 397 |         0 | NULL | NULL      | 2013-02-18 21:35:59 | NULL                | post new thread | swampBoogie | NULL     | NULL         |
    +-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+----------+--------------+
    6 rows in set
    
    mysql>
    instead of:
    Code:
    +-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+----------+--------------+
    | ID  | connected | id   | connected | datum               | datum_update        | title           | author      | message  | last_replies |
    +-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+----------+--------------+
    | 392 |         0 |  395 |       392 | 2012-08-16 11:19:16 | 2013-01-24 01:17:00 | help me         | Sandra      | regards  | Sammy        |
    | 396 |         0 | NULL | NULL      | 2013-02-16 21:28:42 | NULL                | new thread      | Rudy        | NULL     | NULL         |
    | 397 |         0 | NULL | NULL      | 2013-02-18 21:35:59 | NULL                | post new thread | swampBoogie | NULL     | NULL         |
    +-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+----------+--------------+

  13. #13
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Hello everyone.

    Thanks all in advance for any help and suggestions.

    This version where use LEFT OUTER JOIN -suggestion of r397- return this new output, the row # 2 -b.id = 394- is too ....

    Code:
    mysql> SELECT
    	A.ID,
    	A.connected,
    	B.id,
    	B.connected,
    	A.datum,
    	B.datum_update,
    	A.title,
    	A.author,
    	B.message,
    	B.last_replies
    FROM
    	forums a
    LEFT OUTER JOIN forums b ON a.id = b.connected
    LEFT OUTER JOIN (
    	SELECT
    		connected,
    		max(datum_update) AS max_date
    	FROM
    		forums
    	GROUP BY
    		connected
    ) dt ON (b.connected, b.datum_update) = (dt.connected, dt.max_date)
    WHERE
    	A.connected = 0
    ORDER BY
    	dt.max_date DESC;
    +-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+----------+--------------+
    | ID  | connected | id   | connected | datum               | datum_update        | title           | author      | message  | last_replies |
    +-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+----------+--------------+
    | 392 |         0 |  395 |       392 | 2012-08-16 11:19:16 | 2013-01-24 01:17:00 | help me         | Sandra      | regards  | Sammy        |
    | 392 |         0 |  394 |       392 | 2012-08-16 11:19:16 | 2012-08-24 00:08:00 | help me         | Sandra      | hi there | admin        |
    | 396 |         0 | NULL | NULL      | 2013-02-16 21:28:42 | NULL                | new thread      | Rudy        | NULL     | NULL         |
    | 397 |         0 | NULL | NULL      | 2013-02-18 21:35:59 | NULL                | post new thread | swampBoogie | NULL     | NULL         |
    +-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+----------+--------------+
    4 rows in set
    
    mysql>

  14. #14
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Code:
    mysql> SELECT
            A.ID,
            A.connected,
            B.id,
            B.connected,
            A.datum,
            B.datum_update,
            A.title,
            A.author,
            B.message,
            B.last_replies
    FROM
            forums a
    LEFT OUTER JOIN forums b ON a.id = b.connected
    LEFT OUTER JOIN (
            SELECT
                    connected,
                    max(datum_update) AS max_date
            FROM
                    forums
            GROUP BY
                    connected
    ) dt ON (b.connected, b.datum_update) = (dt.connected, dt.max_date)
    WHERE
            a.connected = 0
    AND (
            dt.connected IS NOT NULL
            OR b.id IS NULL
    )
    ORDER BY
            dt.max_date DESC;
    +-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+---------+--------------+
    | ID  | connected | id   | connected | datum               | datum_update        | title           | author      | message | last_replies |
    +-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+---------+--------------+
    | 392 |         0 |  395 |       392 | 2012-08-16 11:19:16 | 2013-01-24 01:17:00 | help me         | Sandra      | regards | Sammy        |
    | 396 |         0 | NULL | NULL      | 2013-02-16 21:28:42 | NULL                | new thread      | Rudy        | NULL    | NULL         |
    | 397 |         0 | NULL | NULL      | 2013-02-18 21:35:59 | NULL                | post new thread | swampBoogie | NULL    | NULL         |
    +-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+---------+--------------+
    3 rows in set
    
    mysql>


Bookmarks

Posting Permissions

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