Traversal methods

Miguel61 · February 18, 2013, 11:24am

Hello everyone.

Thanks all in advance for any help and suggestions.

This is table «forums» in MySQL:

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?

+-----+-----------+--------------------------+----------------------+---------+--------+----------+--------------+
| 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:

mysql&gt; 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&gt;

swampBoogie · February 18, 2013, 12:46pm


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)

r937 · February 18, 2013, 12:50pm

of course you can

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

Miguel61 · February 18, 2013, 1:04pm

swampBoogie:


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:

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>

Miguel61 · February 18, 2013, 1:09pm

r937:

of course you can

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?

swampBoogie · February 18, 2013, 1:10pm

A spelling mistake on my part, it should be


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

Miguel61 · February 18, 2013, 1:16pm

Oh, jolly good. Thanks!

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>

r937 · February 18, 2013, 2:05pm

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

Miguel61 · February 18, 2013, 2:09pm

Of course boss

Miguel61 · February 18, 2013, 8:41pm

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:

mysql&gt; 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&gt; 

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

r937 · February 18, 2013, 9:38pm

use LEFT OUTER JOINs

Miguel61 · February 18, 2013, 9:46pm

Thank you, but…

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:

+-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+----------+--------------+
| 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         |
+-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+----------+--------------+

Miguel61 · February 19, 2013, 1:33pm

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 …

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>

Miguel61 · February 20, 2013, 6:40pm

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>