I am writing a custom forum, and am using a shared web host with mySQL 4.0.24 installed. As such, it doesn't support subqueries, and since it's shared, I don't want to put more strain on the system than I have to. So, given those constraints, I'd like to find a way to gather all the data I need from a normalized data schema in a single query. My problem is in the query that gathers the topics to be shown. I need:
- all of the information for the topic
- the author, time, and summary of the first post
- the author, time, and summary of the last post
- the number of replies
I additionally need to be able to dynamically sort the results by a column selected by the user, including topic name, last reply date, author, and number of replies, though that's a secondary issue. Here is a dump of the relevant tables:[code]
-- Table structure for table posts
CREATE TABLE posts (
postId bigint(20) unsigned NOT NULL auto_increment,
topicId bigint(20) unsigned NOT NULL default '0',
userId int(10) unsigned NOT NULL default '0',
postTime datetime NOT NULL default '0000-00-00 00:00:00',
summary varchar(192) NOT NULL default '',
PRIMARY KEY (postId),
KEY topicId (topicId),
KEY userId (userId),
KEY postTime (postTime),
) TYPE=MyISAM;
--
-- Table structure for table topics
CREATE TABLE topics (
topicId int(10) unsigned NOT NULL default '0',
topicName varchar(64) NOT NULL default '',
userId int(11) NOT NULL default '0',
UNIQUE KEY topicId (topicId),
KEY topicName (topicName)
) TYPE=MyISAM;
--
-- Table structure for table users
CREATE TABLE users (
userId int(10) unsigned NOT NULL auto_increment,
username varchar(32) NOT NULL default '',
PRIMARY KEY (userId),
UNIQUE KEY username (username),
)
[/code]Other information: the topicId is set to the postId of the first post in the topic, so the first post is easy to get (postId = topicID)
I don't really need the query written for me, but I need to know if I can do what I am trying to do with a single query; if so, the basic structure of the query (in particular how to select the author and summary of the most recent reply); and if not, the fastest way to accomplish this without subqueries.
If it helps, here's a working query that does all of the above except selecting the last reply's author and summary (u2.username is equal to u1.username).
SELECT
topics.*,
p1.postId AS firstId,
p1.summary,
p1.postTime AS postTime,
COUNT(p2.postId) AS rcount,
MAX(p2.postId) AS lastReplyId,
MAX(p2.postTime) AS lastReply,
u1.username AS authorName
FROM topics
LEFT JOIN posts p1 on (p1.postId=topics.topicId)
LEFT JOIN posts p2 on (p2.topicId=topics.topicId)
LEFT JOIN users u1 on (p1.userId=u1.userId)
LEFT JOIN users u2 on (p2.userId=u2.userId)
GROUP BY p2.topicId
TIA!
Jason
