USE tawikisource_p;

​

SELECT DISTINCT

    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 -- Ensure it's in the main namespace (article space)

#    AND 

    p.page_is_redirect = 0 -- Exclude redirects

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

    AND EXISTS (

        SELECT 1

        FROM revision r2

        JOIN comment c2 ON r2.rev_comment_id = c2.comment_id

        WHERE r2.rev_page = p.page_id

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

    )

    AND NOT 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 r3.rev_timestamp > (

            SELECT MAX(r2.rev_timestamp)

            FROM revision r2

            JOIN comment c2 ON r2.rev_comment_id = c2.comment_id

            WHERE r2.rev_page = p.page_id

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

        )

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

    );