I know there's a bunch of threads similar to this and I looked over many, but was unable to find a solution. So, I have one table called "forum" that houses my Categories (num_type=0), Threads (num_type=1), and Posts (num_type=2). There is a column num_parent that will relate the Thread/Post to the parent Category/Thread. With the query that I am having issues with, I am trying to get all of the Categories, the total Threads inside of each Category, and the total Posts inside the Threads of each Category. To do this, I am trying:
select F.*
, count(F2.num_Forum) as num_ThreadCount
, count(F3.num_Forum) as num_PostCount
, max(F3.dte_Created) as dte_LastPost
from forum F
left outer join forum F2
on F.num_Forum=F2.num_Parent and F2.num_Type=1
left outer join forum F3
on F2.num_Forum=F3.num_Parent and F3.num_Type=2
where F.num_Type=0 and F.num_Inactive=0
group by F.num_Forum
order by F.num_Sticky desc, F.dte_Created desc
The dte_LastPost and num_PostCount are coming out correctly. The num_ThreadCount is not. If I break up the query into two seperate queries:
select F.*
, count(F2.num_Forum) as num_ThreadCount
from forum F
left outer join forum F2
on F.num_Forum=F2.num_Parent and F2.num_Type=1
where F.num_Type=0 and F.num_Inactive=0
group by F.num_Forum
order by F.num_Sticky desc, F.dte_Created desc
select count(F3.num_Forum) as num_PostCount
from forum F2
left outer join forum F3
on F2.num_Forum=F3.num_Parent and F3.num_Type=2
where F2.num_Type=1 and F2.num_Inactive=0
group by F2.num_Forum
order by F2.num_Sticky desc, F2.dte_Created desc
I am getting the correct count for each. But, I need to combine these somehow so that I know which Category the num_PostCount corresponds to. The big difference that I see with doing it separately is that in the second query, I am able to do group by F2.num_Forum. I tried adding it to the conglomerate query, but it did not fix my issue. Anyone know what I need to do to fix my first query?
Ok, I was able to figure out the query to link the three tables WITHOUT putting a column in the Posts table to link it directly to the Category. Instead, Categories links to Threads which links to Posts, down and up the chain.
select C.*
, count(TP.num_Thread) as num_ThreadCount
, coalesce(sum(TP.num_PostCount), 0) as num_PostCount
from forum_categories C
left join
(select count(P.num_Post) as num_PostCount,
T.num_Thread, T.num_CParent, T.num_Inactive
from forum_threads T
left outer join forum_posts P
on P.num_TParent=T.num_Thread and P.num_Inactive=0
where T.num_Inactive=0
group by T.num_Thread) TP
on C.num_Category=TP.num_CParent and TP.num_Inactive=0
where C.num_Inactive=0
group by C.num_Category
order by C.num_Sticky desc, C.dte_Created desc