slight tweak of my query to make it get the correct results ?

[jasonc310771]

SELECT a.created_on AS a, a.topic_id AS a, b.topic_id AS b, c.category_id AS c
FROM `forum_comments` AS a, `forum_topics` AS b, `forum_category` AS c
WHERE a.topic_id = b.topic_id AND b.category_id = c.category_id AND a.confirmed = '1' AND b.confirmed = '1' AND c.confirmed = '1' ORDER BY a.created_on DESC


the first 'a' ok this may be wrong so may change it if i have to but the first 'a' is the date of the post made the data is taken from the 'forum_comments' table

the next 'a' is the 'topic_id' from the 'forum_comments' table

the 'b' is the topic_id from the 'forum_topic' table

the 'c' is the category_id from the 'forum_category'

a--------------------a--b--c----comment
2008-12-11 18:45:04 41 41 3----most recent post in category 3
2008-12-11 18:44:56 40 40 3----the post before the most recent in category 3
2008-12-11 18:44:51 39 39 3----the post before the previous in category 3
2008-12-11 18:44:39 38 38 1----most recent post in cat, 1
2008-12-11 18:44:24 37 37 2----most recent post in cat, 2
2008-12-08 21:51:00 37 37 2----the post before the most recent in category 2
2008-11-01 00:11:00 36 36 2----the post before the previous in category 2

the results should return....

a--------------------a--b--c----comment
2008-12-11 18:45:04 41 41 3----most recent post in category 3
2008-12-11 18:44:39 38 38 1----most recent post in cat, 1
2008-12-11 18:44:24 37 37 2----most recent post in cat, 2

the most recent result where the comments refer to each of the categorys, so only one result for cat 1 and cat 2 and cat 3 and all the rest.

so there will be what seems missing posts but these are just replies made in other topics prior to the most recent.



CREATE TABLE IF NOT EXISTS `forum_category` (
`category_id` bigint(20) NOT NULL auto_increment,
`confirmed` text,
`category` text NOT NULL,
`created_by` text NOT NULL,
`created_on` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `forum_category`
--

INSERT INTO `forum_category` (`category_id`, `confirmed, `category`, `created_by`, `created_on`) VALUES
(1, '1', 'Your stories', 'Admin', '0000-00-00 00:00:00'),
(2, '1', 'Something else', 'jason', '0000-00-00 00:00:00'),
(3, '1', 'Some other random category', 'Admin', '0000-00-00 00:00:00');

-- --------------------------------------------------------

--
-- Table structure for table `forum_comments`
--

CREATE TABLE IF NOT EXISTS `forum_comments` (
`postcounter` bigint(20) NOT NULL auto_increment,
`topic_id` bigint(20) NOT NULL default '0',
`comment` longtext NOT NULL,
`username` varchar(65) NOT NULL default '',
`created_on` datetime NOT NULL default '0000-00-00 00:00:00',
`confirmed` text,
KEY `postcounter` (`postcounter`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=54 ;

--
-- Dumping data for table `forum_comments`
--

INSERT INTO `forum_comments` (`postcounter`, `topic_id`, `comment`, `username`, `created_on`, `confirmed`) VALUES
(53, 37, 'mmm', 'Jason', '2008-12-08 21:51:00', '1'),
(52, 36, '22222222222222', 'Jason', '0000-00-00 00:00:00', '1');

-- --------------------------------------------------------

--
-- Table structure for table `forum_topics`
--

CREATE TABLE IF NOT EXISTS `forum_topics` (
`topic_id` bigint(20) NOT NULL auto_increment,
`category_id` bigint(20) NOT NULL default '0',
`topic` longtext NOT NULL,
`username` text NOT NULL,
`created_on` datetime NOT NULL default '0000-00-00 00:00:00',
`confirmed` text,
KEY `topic_id` (`topic_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=38 ;

--
-- Dumping data for table `forum_topics`
--

INSERT INTO `forum_topics` (`topic_id`, `category_id`, `topic`, `username`, `created_on`, `confirmed`) VALUES
(36, 2, '2', 'Jason', '2008-12-08 21:42:39', '0'),
(37, 2, 'mmmmmm', 'Jason', '2008-12-08 21:51:00', '1');