3 Table Join

daffy_dowden · #1 (**permalink**) 11-11-05, 07:15

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,
Richard

ivon · #2 (**permalink**) 11-11-05, 08:51

Quote:

Originally Posted by daffy_dowden

SELECT COUNT('tblthread.threadID') AS postsCount

There shouldn't be apostrophes around the column name.

madafaka · #3 (**permalink**) 11-11-05, 08:53

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:

Code:

SELECT COUNT(*) AS postsCount 
FROM tblThread, tblTopic, tblForum
WHERE tblThread.topicID = tblTopic.topicID 
AND tblForum.forumID = tblTopic.forumID 
AND tblForum.forumID=1

madafaka · #4 (**permalink**) 11-11-05, 08:56

Quote:

Originally Posted by ivon

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.

ivon · #5 (**permalink**) 11-11-05, 09:08

True, I stand corrected.

daffy_dowden · #6 (**permalink**) 11-11-05, 09:22

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.

madafaka · #7 (**permalink**) 11-11-05, 09:56

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:

Code:

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

Code:

SELECT COUNT(*) AS postsCount 
FROM tblThread, tblTopic, tblForum
WHERE tblThread.topicID = tblTopic.topicID 
AND tblForum.forumID = tblTopic.forumID 
AND tblForum.forumID=1;

Jelly Link · #8 (**permalink**) 11-23-05, 02:15

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

Internet Services

Web Development

Digital Marketing

Consumer Tech