Trying to avoid subqueries

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Trying to avoid subqueries

Post 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?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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)
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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 ;
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

worked wonders. Thanks alot Tim :0
Post Reply