nested select not working with MySQL

[junkjones]

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=&quot;#session.ds#&quot; Name=&quot;GetForums&quot;>
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!

 

[Bromrrrrr]

It won't work, nested queries and MySQL don't mix :-(.
From the MySQL manual:

The following will not yet work in MySQL:

SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2 where table1.id=table2.id);


I'm afraid you're gonna have to rewrite your script without nested queries and try to programmaticaly get the results you need.

Good luck!

Brommrrrr

 

[strantheman]

Are you kidding me?

You can't do agregates or nests in MySQL? I had no idea.. are you serious?

Im baffled, truly. Is this the case? I guess its good to know... YIKES!

 

[Bromrrrrr]

Would I lie to you? ...I'm dead serious. Future versions will support nesting but I have no idea what version that will be.