[grabit]

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

[msquared]

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

[grabit]

Quote:

Originally Posted by mmccarthy

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

[msquared]

Quote:

Originally Posted by grabit

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

[grabit]

Quote:

Originally Posted by mmccarthy

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

[msquared]

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

Mary

[grabit]

Quote:

Originally Posted by mmccarthy

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

[msquared]

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

[grabit]

Quote:

Originally Posted by mmccarthy

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

[msquared]

Quote:

Originally Posted by grabit

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

[grabit]

Quote:

Originally Posted by mmccarthy

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

[msquared]

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

Is posttype a field in the threads table?

[grabit]

Quote:

Originally Posted by mmccarthy

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]

[msquared]

Quote:

Originally Posted by grabit

my msm is [email removed]

Always use PM's to impart this kind of information.

I've sent you an invitation.

Mary

[msquared]

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