SET

  @c = 'American_screenwriters';

SELECT

  pg1.page_title

FROM page AS pg1

JOIN categorylinks AS cl1 ON cl1.cl_from = pg1.page_id

JOIN (

  SELECT cl2.cl_to

    FROM

      categorylinks AS cl2

    WHERE

      cl2.cl_from = cl1.cl_from

      AND cl2.cl_to IN (

        SELECT

          page_title

        FROM

          page pg3

          JOIN categorylinks AS cl3 ON pg3.page_id = cl3.cl_from

        WHERE

          cl3.cl_to = @c

          AND cl3.cl_type = 'subcat'

      )

) AS subs

WHERE

  cl1.cl_to = @c

  AND cl1.cl_type = 'page'

LIMIT

  100;