MySQL COUNT () of all messages by certain criteria?

Question

I am losing my hair, trying to understand what I am doing wrong, let me explain abit about my MySQL structure (so that you better understand) before I move on to the question.

I have a simple PHP forum and I have a column in both tables (for messages and topics) with the name "deleted" if it is 0, which means its display (considered not deleted / exists), or if it is 1 , it is hidden (considered remote / does not exist) - bool / lean.

Now the "specific criteria" that I'm talking about ... I want to get the total number of posts in a particular forum using its id (forum_id), ensuring that it only considers posts that are not deleted (deleted = 0), and their parent topics are not deleted (deleted = 0).

Column / table names are self-evident (see my efforts below for them, if necessary).

I 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 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 of them do not meet specific criteria.

Appreciate all the help! :)

Answer 1

 SELECT COUNT(*) AS total FROM forum_topics JOIN forum_posts ON topic_id WHERE forum_topics.deleted = 0 AND forum_posts.deleted = 0 AND forum_posts.forum_id = '{$forum_id}' 

This will merge the two tables using topic_id and will return results that match the criteria of both tables, deleted = 0 and the desired forum identifier.

Answer 2

I'm not sure I understand your question, but you can start with the following query and configure it to do what you want:

 SELECT COUNT(*) FROM topic_posts AS TP WHERE forum_id='{$forum_id}' AND deleted=0 AND -- Count topic posts that are not deleted and... NOT EXISTS ( -- make sure they are not for a deleted forum SELECT * FROM forum_posts WHERE forum_id=TP.forum_id and deleted<>0) 

I have added comments to the request to help you.