This is driving me up the bleedin' WALL!!...

OK..

I'm upsizing some ASP BBS software I wrote ages ago from Access to MySQL...

all was going really well until the following SQL (which worked lovely with Access) ...

SELECT tblSection.SectionName, tblSection.Order, tblForum.ForumID, tblForum.Name, tblForum.Description, Max(tblPost.Date) AS LastPost, Count(tblPost.PostID) AS Posts, Count(tblThread.ThreadID) AS Threads FROM (tblSection LEFT JOIN (tblForum LEFT JOIN tblThread ON tblForum.ForumID = tblThread.ForumID) ON tblSection.SectionID = tblForum.SectionID) LEFT JOIN tblPost ON tblThread.ThreadID = tblPost.ThreadID GROUP BY tblSection.SectionName, tblSection.Order, tblForum.ForumID, tblForum.Name, tblForum.Description ORDER BY tblSection.Order

produced the following error with MySQL...

Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
[TCX][MyODBC]You have an error in your SQL syntax near '(tblForum LEFT JOIN tblThread ON tblForum.ForumID = tblThread.ForumID) ON tblSec' at line 1

Any help would be INCREDIBLY gratefully received as I've been on it for about 5 hours now and can't find a solution!!

All the best

Simon

 

Sorry Si but I think a rethink is needed.

A max(date) on the postings table , count on the postings table - will run like a dog.

A max is an index range scan on the date field (assuming its indexed) whereas a count is a full table scan.

I presume this is for displaying the section headers - date of last post, total posts in section etc.

What you need to do is store the date of last post and count of posts against the section rather than calculating them on the fly.

Generally (not always) when you have to write complicated SQL it means the basic database design needs refining.

Appreciate the question was how to make it work in mySQL rather than Access, but hey, just my 2p worth as a certified DBA

 

To be honest Lee I think you're right.

It is what I was planning to do originally, but just wanted to trial it to see what the overhead would be...

But another opinion, etc.. I think you're right.

Just didn't like the untidyness of having a table to store the info, but it's not exactly mission critical information, and I can always include an admin function to re-calc anyway.

Thanks for the advice.. if you can think of a way to restructure that SQL to get rid of the parentheses I would be grateful though, just so I can test it.

Cheers

Simon