Hi Everyone,
I purchased an application which was written for Access, but I am running it on MySQL. I have converted most of the queries, simply by changing the case of variables, but there is one that is a little more complicated than that that is still causing me problems. It works fine with Access, but with MySQL I get a Syntax Error with the nested Select. Is there a better way to write this that will work with MySQL? Here is the code:
<cfquery datasource="#session.ds#" Name="GetForums">
Select c.CatID, c.Category, f.ForumID,f.CategoryID,f.ForumName,f.Description,f.ForumOrder, f.forumpassword,
(Select count(T.TopicID)
from Topics T , messages m
where T.ForumID = f.ForumID
and m.forumid = f.forumid
and t.topicid = m.topicid
and m.forumid = f.forumid
and m.isapproved = 'y') as TopicTotals
from Categories c, Forums f
where c.catid = f.CategoryID
Order by c.CatID,f.ForumOrder,f.ForumID
</cfquery>
Any help is appreciated!
I purchased an application which was written for Access, but I am running it on MySQL. I have converted most of the queries, simply by changing the case of variables, but there is one that is a little more complicated than that that is still causing me problems. It works fine with Access, but with MySQL I get a Syntax Error with the nested Select. Is there a better way to write this that will work with MySQL? Here is the code:
<cfquery datasource="#session.ds#" Name="GetForums">
Select c.CatID, c.Category, f.ForumID,f.CategoryID,f.ForumName,f.Description,f.ForumOrder, f.forumpassword,
(Select count(T.TopicID)
from Topics T , messages m
where T.ForumID = f.ForumID
and m.forumid = f.forumid
and t.topicid = m.topicid
and m.forumid = f.forumid
and m.isapproved = 'y') as TopicTotals
from Categories c, Forums f
where c.catid = f.CategoryID
Order by c.CatID,f.ForumOrder,f.ForumID
</cfquery>
Any help is appreciated!