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