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

    +-----+---------------------+---------------------+-----------------+-------------+-----------------------+--------------+-----------+