Hi,
I have this table:
CREATE TABLE `messages` (
`messageID` int(11) unsigned NOT NULL auto_increment,
`threadID` int(11) unsigned,
`fromUserName` varchar(50) default NULL,
`toUserName` varchar(50) default NULL,
`subject` varchar(255) default NULL,
`body` text,
`dateSent` datetime,
`isRead` tinyint(4) default '0',
PRIMARY KEY (`messageID`),
KEY `fromUserName` (`fromUserName`),
KEY `toUserName` (`toUserName`),
KEY `threadID` (`threadID`)
) TYPE=InnoDB ;
which is planned so that I can use threaded messages in a forum type
of app.
Here is what I would like to get hold of in one select, if possible!
Given a userName and a threadID,
fromUserName of the first message in the thread
subject of the first message in the thread
dateSent of the last message in the thread
boolean - if any of the messages in the thread are unread where
userName == messages.toUserName
Is this possible? I have mySql 4.1 so subselects are OK. I could do it
with another table called threads and a bit of denormalisation, but
would this be necessary?
Any suggestions would be very welcome,
Thanks,
John Pedersen