Page 1 of 1
Trying to avoid subqueries
Posted: Sun May 01, 2005 12:04 pm
by John Cartwright
Code: Select all
$result = $db->sql("SELECT * FROM topics LEFT JOIN posts ON posts.topic_id = topics.id
WHERE topics.date >= '". $fc->FirstSecond() ."' AND topics.date < '". $fc->FirstSecondDay(1) ."' AND topics.cid = 2 AND topics.uid = 1");
What I'm trying to accomplish is grab all the topics and the first post of that topic.
Any thoughts?
Posted: Sun May 01, 2005 2:29 pm
by timvw
it's easier if you show us how the tables look like... (especially the posts table)
currently i'm thinking something like adding another posts table to the join
and then p1.post_id = min(p2.post_id)
Posted: Sun May 01, 2005 2:50 pm
by John Cartwright
Code: Select all
CREATE TABLE `posts` (
`topic_id` int(5) NOT NULL default '0',
`post_id` int(5) NOT NULL auto_increment,
`uid` int(5) NOT NULL default '0',
`post` text NOT NULL,
`date` int(10) NOT NULL default '0',
PRIMARY KEY (`post_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 ;
and
Code: Select all
CREATE TABLE `topics` (
`id` int(5) NOT NULL auto_increment,
`title` text NOT NULL,
`uid` int(5) NOT NULL default '0',
`date` int(10) NOT NULL default '0',
`cid` int(5) NOT NULL default '0',
`pic_fp` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 ;
Posted: Sun May 01, 2005 3:07 pm
by timvw
at first sight, the following seems to do it:
Code: Select all
SELECT *
FROM posts
INNER JOIN topics ON topics.id = posts.topic_id
GROUP BY topics.id
Posted: Mon May 02, 2005 1:34 pm
by John Cartwright
worked wonders. Thanks alot Tim :0