Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
John Cartwright
Site Admin
Posts: 11470 Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:
Post
by John Cartwright » Sun May 01, 2005 12:04 pm
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 » Sun May 01, 2005 2:29 pm
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)
John Cartwright
Site Admin
Posts: 11470 Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:
Post
by John Cartwright » Sun May 01, 2005 2:50 pm
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 » Sun May 01, 2005 3:07 pm
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