3 Table Join

I need to perform a 3 table join but my sql is a little rusty and I was never that good at Joins anyway!

I'll try and explain the basic DB layout. It's for a very simple forum board I'm making, I have 3 tables at the moment, one contains the messages which belong to a topics, which have their own table. And the topics all belong to a table listing all the forums.

tblThread
threadID PK
topicID FK
Author
Message
MessageDate

tblTopic
topicID PK
forumID FK
Subject
numViews

tblForum
forumID PK
forumName
forumDescription
forumOwner

What I'm trying to achieve is to get the number threads belonging to a forum.

So far my attempt at an sql query looks like this:

SELECT COUNT('tblthread.threadID') AS postsCount
FROM tblThread
INNER JOIN tblTopic ON tblThread.topicID = tblTopic.topicID
INNER JOIN tblForum ON tblForum.forumID = tblTopic.forumID
WHERE tblForum.forumID=1

but that's missing an operator somewhere. Anyone know where i'm going wrong?

Thanks,
RichardSELECT COUNT('tblthread.threadID') AS postsCount
There shouldn't be apostrophes around the column name.|||your statement seems be fine. what database server do you use? there might be some syntax problem (INNER JOIN or something) try this one, but basically that's the same:

SELECT COUNT(*) AS postsCount
FROM tblThread, tblTopic, tblForum
WHERE tblThread.topicID = tblTopic.topicID
AND tblForum.forumID = tblTopic.forumID
AND tblForum.forumID=1|||There shouldn't be apostrophes around the column name.
actually this shouldn't be issue. you can put whatever as count() argument. it can be column name, *, or some constant such as 1 or 'XXX'. in this case it's constant string which shouldn't affect result.|||True, I stand corrected.|||Hi Guys,
Thanks for the help. I'm using MS Access as the DB. I tried your suggestion madafaka but I'm still getting the same error:

"Microsoft JET Database Engine (0x80040E14)
Syntax error in FROM clause."

Is this an error specific to access?

Thanks.|||daffy_dowden,
I created all 3 tables in MS Access (I think it's 2000 version)

I created Query (Create Query in Design view) using (SQL View)
simply: pasted code posted before

your code returned exactly the same error as you described. I don't know why, but what you can expect from MS Access :-)
Then I used (Design view) and it generated this code, which works fine:

SELECT count(*)
FROM tblForum
INNER JOIN (tblThread INNER JOIN tblTopic ON tblThread.topicID = tblTopic.topicID) ON tblForum.forumID = tblTopic.forumID
WHERE tblForum.forumID=1;

code I posted before worked fine, so I don't know why you received an error

SELECT COUNT(*) AS postsCount
FROM tblThread, tblTopic, tblForum
WHERE tblThread.topicID = tblTopic.topicID
AND tblForum.forumID = tblTopic.forumID
AND tblForum.forumID=1;|||I guess Access joins tables step by step : tblThread join tblTopic, and then join tblForum

SELECT COUNT('tblthread.threadID') as postsCount
FROM
(tblThread INNER JOIN tblTopic ON tblThread.topicID = tblTopic.topicID)
INNER JOIN tblForum ON tblForum.forumID = tblTopic.forumID
WHERE tblForum.forumID=1