USE tawikisource_p;

​

SELECT

    p.page_title AS Title,

    a.actor_name AS user,

    c.comment_text AS comment

FROM page p

JOIN revision r ON p.page_id = r.rev_page

JOIN comment c ON r.rev_comment_id = c.comment_id

JOIN actor a ON r.rev_actor = a.actor_id

WHERE

#    p.page_namespace = 0

#    AND 

    p.page_is_redirect = 0

    AND p.page_title LIKE "மயிலை_சீனி._வேங்கடசாமி_ஆய்வுக்_களஞ்சியம்_10.pdf/%"

    AND r.rev_timestamp = (

        SELECT MAX(r2.rev_timestamp)

        FROM revision r2

        WHERE r2.rev_page = p.page_id

    )

    AND EXISTS (

        SELECT 1

        FROM revision r3

        JOIN comment c3 ON r3.rev_comment_id = c3.comment_id

        WHERE r3.rev_page = p.page_id

        AND (c3.comment_text LIKE "%மேம்படுத்த வேண்டியவை%" OR c3.comment_text LIKE "%Problematic%")

    )

    AND NOT EXISTS (

        SELECT 1

        FROM revision r4

        JOIN comment c4 ON r4.rev_comment_id = c4.comment_id

        WHERE r4.rev_page = p.page_id

        AND r4.rev_timestamp > (

            SELECT MAX(r5.rev_timestamp)

            FROM revision r5

            JOIN comment c5 ON r5.rev_comment_id = c5.comment_id

            WHERE r5.rev_page = p.page_id

            AND (c5.comment_text LIKE "%மேம்படுத்த வேண்டியவை%" OR c5.comment_text LIKE "%Problematic%")

        )

        AND NOT (c4.comment_text LIKE "%மேம்படுத்த வேண்டியவை%" OR c4.comment_text LIKE "%Problematic%")

    );