By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,955 Members | 1,756 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,955 IT Pros & Developers. It's quick & easy.

getting reply totals in forum

P: 22
Hi Peoples
I must first, again thank Mary for her help over the last couple of days.
I have the following sql that get me all the info i want so far, but have hit a wall when it comes to adding the number of replies per thread.
In this statement the posttype refered to is a colum that holds 1 for new threads and 2 for replies.
What i need is to rewrite the statement to return me everything i have so far plus a count of replies when the topicID of posttype 2's equals the topicID of the related posttype 1
As ususal any help in this would be very much appreciated and my existing statement follows
Expand|Select|Wrap|Line Numbers
  1. <!---Query db for page info--->
  2.     <cfquery name="showtopics" datasource="#dsn#">
  3.     SELECT threadID, posttype, threads.catID, topic, topicID, memberID, username, postdate, categories.catID, category
  4.     FROM threads, categories
  5.     WHERE threads.catID=#url.catID# AND categories.catID=#url.catID# AND posttype=1
  6.     ORDER BY ThreadID DESC
  7.     </cfquery>
thanks in advance
Grabit
Mar 11 '07 #1
Share this Question
Share on Google+
14 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1. <!---Query db for page info--->
  2. <cfquery name="showtopics" datasource="#dsn#">
  3. SELECT threadID, posttype, threads.catID, topic, topicID, 
  4. memberID, username, postdate, category, CountReplies
  5. FROM (threads INNER JOIN categories 
  6. ON threads.catID = categories.catID) INNER JOIN 
  7. (SELECT topicID, Sum(IIf([posttype]=2,1,0) As CountReplies
  8. FROM threads GROUP BY topicID) As T2
  9. ON threads.topicID = T2.topicID
  10. WHERE threads.catID=#url.catID# 
  11. AND posttype=1
  12. ORDER BY ThreadID DESC
  13. </cfquery>
  14.  
Mary
Mar 11 '07 #2

P: 22
Try this ...

Expand|Select|Wrap|Line Numbers
  1. <!---Query db for page info--->
  2. <cfquery name="showtopics" datasource="#dsn#">
  3. SELECT threadID, posttype, threads.catID, topic, topicID, 
  4. memberID, username, postdate, category, CountReplies
  5. FROM (threads INNER JOIN categories 
  6. ON threads.catID = categories.catID) INNER JOIN 
  7. (SELECT topicID, Sum(IIf([posttype]=2,1,0) As CountReplies
  8. FROM threads GROUP BY topicID) As T2
  9. ON threads.topicID = T2.topicID
  10. WHERE threads.catID=#url.catID# 
  11. AND posttype=1
  12. ORDER BY ThreadID DESC
  13. </cfquery>
  14.  
Mary
Hi Mary
Again thanks for the help and the prompt reply
If i run that i get the following error (i see a ( in front of the 2nd SELECT statement - i have tried removing it and no difference)

error is
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

The error occurred in C:\CFusionMX\wwwroot\Forum\topics.cfm: line 37

35 : FROM threads GROUP BY topicID) As T2
36 : ON threads.topicID = T2.topicID
37 : WHERE threads.catID=#url.catID#
38 : AND posttype=1)
39 : ORDER BY ThreadID DESC
Mar 11 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Mary
Again thanks for the help and the prompt reply
If i run that i get the following error (i see a ( in front of the 2nd SELECT statement - i have tried removing it and no difference)

error is
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

The error occurred in C:\CFusionMX\wwwroot\Forum\topics.cfm: line 37

35 : FROM threads GROUP BY topicID) As T2
36 : ON threads.topicID = T2.topicID
37 : WHERE threads.catID=#url.catID#
38 : AND posttype=1)
39 : ORDER BY ThreadID DESC
There shouldn't be a closing bracket after posttype=1
Mar 11 '07 #4

P: 22
There shouldn't be a closing bracket after posttype=1
sorry Mary
i already removed that but i still got the same error
thanks
Grabit
Mar 11 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Try running the query directly in Access and see if you are getting the same error. The problem may be with the parser.

Mary
Mar 11 '07 #6

P: 22
Try running the query directly in Access and see if you are getting the same error. The problem may be with the parser.

Mary
Im sorry Mary but i cannot see how to c&p the existing query into access to run it can you polease help me with how to do that please?
cheers
Grabit
Mar 11 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
Open an Access query window in design view. Don't add any tables. Then switch the view to SQL and copy and paste in the following:

Expand|Select|Wrap|Line Numbers
  1. SELECT threadID, posttype, threads.catID, topic, topicID, 
  2. memberID, username, postdate, category, CountReplies
  3. FROM (threads INNER JOIN categories 
  4. ON threads.catID = categories.catID) INNER JOIN 
  5. (SELECT topicID, Sum(IIf([posttype]=2,1,0) As CountReplies
  6. FROM threads GROUP BY topicID) As T2
  7. ON threads.topicID = T2.topicID
  8. WHERE threads.catID=#url.catID# 
  9. AND posttype=1
  10. ORDER BY ThreadID DESC
  11.  
Mary
Mar 11 '07 #8

P: 22
Open an Access query window in design view. Don't add any tables. Then switch the view to SQL and copy and paste in the following:

Expand|Select|Wrap|Line Numbers
  1. SELECT threadID, posttype, threads.catID, topic, topicID, 
  2. memberID, username, postdate, category, CountReplies
  3. FROM (threads INNER JOIN categories 
  4. ON threads.catID = categories.catID) INNER JOIN 
  5. (SELECT topicID, Sum(IIf([posttype]=2,1,0) As CountReplies
  6. FROM threads GROUP BY topicID) As T2
  7. ON threads.topicID = T2.topicID
  8. WHERE threads.catID=#url.catID# 
  9. AND posttype=1
  10. ORDER BY ThreadID DESC
  11.  
Mary
sorry again Mary
if i open a query window in design view all i get is tables, queries or both tabs - i cannot for the love of me fins=d any reference to sql, if i dont add any tables to the query i just get a window that looks like i could c&p into it but it is greyed out
cheers
Grabit
Mar 11 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
sorry again Mary
if i open a query window in design view all i get is tables, queries or both tabs - i cannot for the love of me fins=d any reference to sql, if i dont add any tables to the query i just get a window that looks like i could c&p into it but it is greyed out
cheers
Grabit
Ok the greyed out window is the Access query view. Once you get to that stage go to the first icon on the toolbar. It should look like a blue right-sided triangle. The little black arrow beside it should give you a drop down list one of which is SQL.

Mary
Mar 11 '07 #10

P: 22
Ok the greyed out window is the Access query view. Once you get to that stage go to the first icon on the toolbar. It should look like a blue right-sided triangle. The little black arrow beside it should give you a drop down list one of which is SQL.

Mary
thanks Mary
got it all pasted in there and everything else and when i tried to save it it gave me nearly the same error
"Syntax error in the FROM clause"
cheers
im not much help here eh?
Grabit
Mar 12 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
Firstly drop the AS from the AS T2 and see if that helps.

Is posttype a field in the threads table?
Mar 12 '07 #12

P: 22
Firstly drop the AS from the AS T2 and see if that helps.

Is posttype a field in the threads table?
Hi yet again Mary
Nope dropped that AS and no difference
yes posttype is a column in the threads table

do you have msm and if so can i contact you there to see if we can speed things up on this please?

my msm is [email removed]
Mar 12 '07 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
my msm is [email removed]
Always use PM's to impart this kind of information.

I've sent you an invitation.

Mary
Mar 12 '07 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
Expand|Select|Wrap|Line Numbers
  1. <!---Query db for page info--->
  2. <cfquery name="showtopics" datasource="#dsn#">
  3. SELECT threadID, posttype, threads.catID, topic, topicID, 
  4. memberID, username, postdate, category, CountReplies
  5. FROM (threads INNER JOIN categories 
  6. ON threads.catID = categories.catID) INNER JOIN 
  7. (SELECT topicID, Sum(IIf([posttype]=2,1,0)) As CountReplies
  8. FROM threads GROUP BY topicID) As T2
  9. ON threads.topicID = T2.topicID
  10. WHERE threads.catID=#url.catID# 
  11. AND posttype=1
  12. ORDER BY ThreadID DESC
  13. </cfquery>
  14.  
There was a closing bracket missing. My error, sorry.

Mary
Mar 12 '07 #15

Post your reply

Sign in to post your reply or Sign up for a free account.