Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
[quote="ReverendDexter"]okay, what did you get when you pull out the "group by p.thread_id"?[/quote]
The same
[quote="feyd"]Can you post the table structures (CREATE TABLE ....) some sample data and the expect output for said data?[/quote]
Sure, here
[syntax="sql"]CREATE TABLE `Post` (
`id` int(8) NOT NULL auto_increment,
`post` text NOT NULL,
`user_id` int(8) NOT NULL default '0',
`thread_id` int(8) NOT NULL default '0',
`smilies` int(1) NOT NULL default '0',
`ip` varchar(30) NOT NULL default '',
`updated` timestamp NOT NULL default CURRENT_TIMESTAMP,
`created` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
FULLTEXT KEY `post` (`post`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;
--
-- Dumping data for table `Post`
--
INSERT INTO `Post` VALUES (1, 'This is the first post.', 1, 1, 1, '81.179.79.90', '2007-09-04 22:20:38', '2007-09-04 22:20:38');
INSERT INTO `Post` VALUES (2, 'This is the second post.', 7, 1, 1, '86.131.249.69', '2007-09-05 16:05:35', '2007-09-05 16:05:35');
INSERT INTO `Post` VALUES (3, 'testers', 1, 10, 1, '86.20.130.212', '2007-11-30 16:52:59', '2007-09-30 16:52:22');
INSERT INTO `Post` VALUES (4, 'That ring tone innit', 1, 11, 1, '86.20.130.212', '2007-09-30 19:13:22', '2007-09-30 19:13:22');
INSERT INTO `Post` VALUES (5, 'Special Ring UP!', 1, 11, 1, '86.20.130.212', '2007-09-30 19:39:49', '2007-09-30 19:39:49');
INSERT INTO `Post` VALUES (6, 'Tester Up!', 1, 2, 1, '86.20.130.212', '2007-09-30 19:40:23', '2007-09-30 19:40:23');
INSERT INTO `Post` VALUES (7, 'Why am I not up? ', 1, 11, 1, '86.20.130.212', '2007-09-30 19:40:35', '2007-09-30 19:40:35');
INSERT INTO `Post` VALUES (23, 'rfgrftg', 1, 10, 1, '86.20.130.212', '2007-10-04 18:19:05', '2007-10-04 18:19:05');
INSERT INTO `Post` VALUES (22, 'make me1', 1, 11, 1, '86.20.130.212', '2007-10-04 18:18:55', '2007-10-04 18:18:55');
INSERT INTO `Post` VALUES (21, 'godamit', 1, 10, 1, '86.20.130.212', '2007-10-04 18:18:20', '2007-10-04 18:18:20');
INSERT INTO `Post` VALUES (20, 'im ere', 1, 11, 1, '86.20.130.212', '2007-10-04 18:18:05', '2007-10-04 18:18:05');
INSERT INTO `Post` VALUES (19, 'im here', 1, 11, 1, '86.20.130.212', '2007-10-04 18:17:59', '2007-10-04 18:17:59');
INSERT INTO `Post` VALUES (18, 'Im new', 1, 11, 1, '86.20.130.212', '2007-09-30 21:34:38', '2007-09-30 21:34:38');
INSERT INTO `Post` VALUES (17, 'TEster should move up', 1, 10, 1, '86.20.130.212', '2007-09-30 21:32:54', '2007-09-30 21:32:54');
--
-- Table structure for table `Thread`
--
CREATE TABLE `Thread` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(100) NOT NULL default '',
`forum_id` int(10) NOT NULL default '0',
`icon_id` int(8) NOT NULL default '0',
`views` int(8) NOT NULL default '0',
`posts` int(8) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;
--
-- Dumping data for table `Thread`
--
INSERT INTO `Thread` VALUES (1, 'Pete Burns', 1, 1, 233, 41);
INSERT INTO `Thread` VALUES (2, 'Dorethy', 1, 2, 18, 55);
INSERT INTO `Thread` VALUES (10, 'tester', 3, 1, 40, 9);
INSERT INTO `Thread` VALUES (11, 'Special Ring', 3, 1, 33, 12);
--
-- Table structure for table `Forum`
--
CREATE TABLE `Forum` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(100) NOT NULL default '',
`description` text NOT NULL,
`icon` int(11) NOT NULL default '0',
`category_id` int(8) NOT NULL default '0',
`order` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
--
-- Dumping data for table `Forum`
--
INSERT INTO `Forum` VALUES (1, 'General Discussion', 'A place to shoot from the hip', 1, 2, 1);
INSERT INTO `Forum` VALUES (2, 'Off Topic Banter', 'A place to spam away like there is no tomorrow.', 1, 2, 2);
INSERT INTO `Forum` VALUES (3, 'Forum Discussion', 'A place to discuss Wuggawoo and its development.', 3, 1, 2);
INSERT INTO `Forum` VALUES (4, 'News Discussion', 'A place to discuss serious news articles.', 1, 1, 1);