Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2007
    Posts
    2

    Angry Unanswered: Get threads sorted by most recent topic addition

    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!

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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 :

    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •