Results 1 to 8 of 8

Thread: 3 Table Join

  1. #1
    Join Date
    Jan 2004
    Location
    Northumberland, England
    Posts
    11

    Question Unanswered: 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,
    Richard

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by daffy_dowden
    SELECT COUNT('tblthread.threadID') AS postsCount
    There shouldn't be apostrophes around the column name.

  3. #3
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    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

  4. #4
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    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.

  5. #5
    Join Date
    Nov 2002
    Posts
    272
    True, I stand corrected.

  6. #6
    Join Date
    Jan 2004
    Location
    Northumberland, England
    Posts
    11
    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.

  7. #7
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    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;

  8. #8
    Join Date
    Dec 2003
    Posts
    39

    Just (.... inner join .... on ....)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •