locked
[MySQL] Last replies RRS feed

  • Question

  • User-908043281 posted

    Hello everyone.

    Thanks all in advance for any help and suggestions.

    I've the table `forums` where register threads, I need in output:

    1. all threads without replies;
    2. for threads with replies, the last reply.

    I tried this query but is a problem: in output not are visible the new threads without replies.

    Please check this:

    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');



    Tuesday, February 19, 2013 4:06 AM

Answers

  • User1872364149 posted

    try this:

    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> 



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 20, 2013 12:17 PM