[MySQL] Last replies

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