[kerkor]

I need help coming up with a particular query...

I need a list of the THREADS sorted by the most recent TOPIC added.

Note: I just recently added the last_updated field to tblThreads so it is not functioning the way I wish,
plus this field is set anytime there is any activity.

Here are the related tables:

CREATE TABLE `tblThreads` (
`threadID` smallint(4) unsigned zerofill NOT NULL auto_increment,
`threadTitle` varchar(255) NOT NULL default '',
`threadCreated` datetime default NULL,
`last_updated` datetime default NULL,
`created_by` varchar(25) default NULL,
PRIMARY KEY (`threadID`)
) ENGINE=MyISAM AUTO_INCREMENT=154 DEFAULT CHARSET=latin1 COMMENT='Forum threads';


CREATE TABLE `tblTopics` (
`topicID` smallint(4) unsigned zerofill NOT NULL auto_increment,
`topicTitle` varchar(255) NOT NULL default '',
`topicBody` text NOT NULL,
`topicPoster` varchar(128) NOT NULL default '',
`topicPosterEmail` varchar(64) NOT NULL default '',
`topicPosted` datetime NOT NULL default '0000-00-00 00:00:00',
`created_by` int(11) NOT NULL default '0',
PRIMARY KEY (`topicID`)
) ENGINE=MyISAM AUTO_INCREMENT=6357 DEFAULT CHARSET=latin1 COMMENT='Thread topics';


CREATE TABLE `tblTopicsThreadsLink` (
`threadID` smallint(4) unsigned zerofill NOT NULL default '0000',
`topicID` smallint(4) unsigned zerofill NOT NULL default '0000',
UNIQUE KEY `UNIQUE` (`threadID`,`topicID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Threads to topics link table';

Thanks you so much for any suggestions or advice or query!

[aschk]

I'm just curious to find out why you have used a mapping table between threads and topics? Seeing as its a 1-to-many relationship between topics and threads I would have thought you wouldn't need to model it the way you have. Just have a topic column in the thread table. A thread can ONLY be part of ONE topic...

You could change the last_updated field to a TIMESTAMP instead with default of CURRENT_TIMESTAMP. This way you don't have to manually update the last_updated.

If you need threads sorted by topic added then as a quick guess at an SQL statement I would say :

select *,
  (select topicPosted 
  FROM tblTopics tblTo 
  JOIN tblTopicsThreadsLink tblTt ON tblTt.topicID=tblTo.topicID
  WHERE tblTt.threadID=tblTh.threadID) as ordDate
 FROM tblThreads tblTh
 ORDER BY ordDate DESC