falseSubscription.sql

-- Find all threads that have zero subscriptions BUT have at least one attachment
SELECT *
FROM (SELECT "Thread".id, "Thread".subject,
             COUNT(DISTINCT(S."threadId")) AS Subscriptions,
             COUNT(A.id) AS Attachments
      FROM "Thread"
               LEFT JOIN "Post" P on "Thread".id = P."threadId"
               LEFT JOIN "Attachment" A on P.id = A."postId"
               LEFT JOIN "Subscription" S on "Thread".id = S."threadId"
      GROUP BY "Thread".id)
         AS COUNTS
WHERE COUNTS.Subscriptions = 0 AND COUNTS.Attachments > 1
ORDER BY COUNTS.Attachments DESC