Will a SQL guru please help me?!

Results 1 to 4 of 4

Thread: Will a SQL guru please help me?!

  1. #1
    rommy Guest

    Default Will a SQL guru please help me?!

    Ok, here&#039s the deal. I have a basic messageboard i&#039m testing in Access. It only consists of two tables, Threads and Posts. On the first page i want to display the title of each thread --&#062; Threads.ThreadTitle. I&#039m iterating through the top 15 Threads to write each thread to the screen. Simple enough.<BR><BR>Ok, next to each thread that gets written to the screen i want to display the total number of posts that were posted to each thread. Something like this:<BR><BR>Anybody need a job? (7)<BR>How should i paint my car? (2)<BR>I need a horse now. (1)<BR><BR>Basically each post in the Posts table has a Postthread field with the ID of the Thread the post was made to. How can i pull out all of the Thread names and then Count the amount of Posts made to each Thread all at once like the example above. I&#039ve tried all kinds of table joins and I can&#039t figure it out. I&#039ve seen this plenty of times so i know it can be done. Is it possible to have a database connection within a database connection? Please help !!??<BR><BR>Thanks.

  2. #2
    Join Date
    Dec 1969

    Default RE: Will a SQL guru please help me?!

    Select Thread,count(*) <BR>From ThreadTable,PostTable<BR>where ThreadTable.ID = PostTable.ID<BR>Group by Thread

  3. #3
    rommy Guest

    Default RE: Will a SQL guru please help me?!

    Dave, thanks for your respose, but it still doesn&#039t seem to work. Here&#039s the original SQL statement used to show just the threads:<BR><BR>SELECT threadid, threadtitle, threadauthor, threaddate FROM threads ORDER BY threaddate DESC<BR><BR>Now here&#039s the new statement, based on your suggestion, for displaying all of the threads plus a count of posts within each thread:<BR><BR>SELECT threads.threadid, threads.threadtitle, threads.threadauthor, threads.threaddate, count(*) FROM threads, posts WHERE threads.threadid = posts.postthread GROUP BY threads.threadid ORDER BY threads.threaddate DESC<BR><BR>Now when i run the script, nothing is being returned, not even the names of the threads. What am i doing wrong?<BR><BR>Thanks.

  4. #4
    rommy Guest

    Default Works now!

    Forget it, it works now. I just forgot to add the other fields to the GROUP BY portion of the statement. Thanks much !!

Posting Permissions

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