Thanks in advance for your time. I have a simple problem here, but cannot seem to get my head around the right way to get the job done. I'm creating a forum. There are three tables which we need concern ourselves with: sections, threads and posts. Sections have threads in them, and users create posts, which are inside of threads. What I want to create is the main page, which lists all of the sections in the database, along with how many threads AND posts each section currently has.
Here's the table layouts:
sections: secID (primary key), secTitle, secDescription
threads: thrID (primary key), thrTitle, thrSectionID (relating to secID)
posts: posID (primary key), posThreadID (relating to thrID), posTitle, posDate, posAuthorID, posText
Here's the sql I've tried (which doesn't work, its only returns one record):
SELECT secID, secTitle, secDescription, COUNT(t.thrID) AS secTotalThreads, COUNT(p.posID) AS secTotalPosts
FROM sections AS s, threads AS t, posts AS p
WHERE t.thrSectionID = s.secID AND p.posThreadID = t.thrID
GROUP BY secID, secTitle, secDescription
This query returns multiple sections AND their correct number of threads, but I cannot figure out how to add in the part for the posts to make it work:
SELECT s.secID, s.secTitle, s.secDescription, COUNT(thrID) AS secTotalThreads
FROM sections AS s, threads AS t
WHERE s.secID = t.thrSectionID
GROUP BY secID, secTitle, secDescription
Thanks again for your help,
Trevor Smith
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
