Kingston Dhasian (2/20/2013)
change your WHERE Clause like below
WHERE A.connected = 0 AND dt.connected IS NOT NULL
thank you for help, but the output is incorrect:
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
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 |
+-----+-----------+-----+-----------+---------------------+---------------------+---------+--------+---------+--------------+
1 row in set
I need this output:
+-----+---------------------+---------------------+-----------------+-------------+-----------------------+--------------+-----------+
| id | datum | datum_update | title | author | message | last_replies | connected |
+-----+---------------------+---------------------+-----------------+-------------+-----------------------+--------------+-----------+
| 395 | 2013-01-24 13:17:27 | 2013-01-24 01:17:00 | help me | Sammy | regards | Sammy | NULL |
| 396 | 2013-02-16 21:28:42 | 2013-02-16 21:28:49 | new thread | Rudy | this is new thread | Rudy | NULL |
| 397 | 2013-02-18 21:35:59 | 2013-02-18 21:36:04 | post new thread | swampBoogie | this is my new thread | swampBoogie | NULL |
+-----+---------------------+---------------------+-----------------+-------------+-----------------------+--------------+-----------+