Hey,

I've been losing my hair trying to figure out what I'm doing wrong, let me explain abit about my MySQL structure (so you get a better understanding) before I go straight to the question.

I have a simple PHP forum and I have a column in both tables (for posts and topics) named 'deleted' if it equals 0 that means its displayed (considered not deleted/exists) or if it equals 1 it hidden (considered deleted/doesn't exist) - bool/lean.

Now, the 'specific criteria' I'm on about...I'm wanting to get a total post count within a specific forum using its id (forum_id), ensuring it only counts posts which are not deleted (deleted = 0) and their parent topics are not deleted either (deleted = 0).

The column/table names are self explanatory (see my efforts below for them - if needed).

I've tried the following (using a 'simple' JOIN):

SELECT COUNT(t1.post_id) 
  FROM forum_posts AS t1, forum_topics AS t2 
 WHERE t1.forum_id = '{$forum_id}' 
   AND t1.deleted = 0 
   AND t1.topic_id = t2.topic_id 
   AND t2.deleted = 0 
 LIMIT 1

I've also tried this (using a Subquery):

SELECT COUNT(t1.post_id) 
  FROM forum_posts AS t1 
 WHERE t1.forum_id = '{$forum_id}' 
   AND t1.deleted = 0 
   AND (SELECT deleted 
          FROM forum_topics 
         WHERE topic_id = t1.topic_id) = 0 
 LIMIT 1

But both don't comply with the specific criteria.

Appreciate all help! :)