Joins

[bob_the _builder]

Hi,

 

I am writing a basic forum. I am struging to grab the last poster for display in the forum index.

 

My table layout is:

 

CREATE TABLE headings ( 
  headingid int(5) NOT NULL auto_increment, 
  heading varchar(50) NOT NULL,
  description varchar(255) NOT NULL,
  PRIMARY KEY (headingid) 
);

CREATE TABLE topics ( 
  topicid int(5) NOT NULL auto_increment, 
  headingid int(5) NOT NULL,
  userid int(5) NOT NULL, 
  views int(5) NOT NULL, 
  topic varchar(50) NOT NULL, 
  PRIMARY KEY (topicid) 
);

CREATE TABLE posts ( 
  postid int(5) NOT NULL auto_increment, 
  topicid int(5) NOT NULL,
  userid int(5) NOT NULL,  
  Post text NOT NULL,
  added datetime NOT NULL,
  viewed int(5) NOT NULL,
  PRIMARY KEY (postid) 
);

 

On the page where the headings are displayed I am trying to show the last poster for each heading.

 

I have tried with no luck, here is what I have at the moment which is no where near correct:

 

	$sql = mysql_query("SELECT u.first_name, u.first_name, p.added
FROM topics as t
LEFT JOIN posts as p ON t.topicid = p.topicid
LEFT JOIN users as u ON u.userid = p.userid
LEFT JOIN headings as h ON h.headingid = t.headingid
WHERE t.headingid = h.headingid
ORDER BY t.topicid DESC LIMIT 1") or die (mysql_error());
while ($row1 = mysql_fetch_array($sql)) {


$sql = mysql_query("SELECT * FROM headings");
while ($row = mysql_fetch_array($sql)) {

 

Thanks

 

[bubblegum.anarchy]

Can anyone confirm this wacky notion:

 

SELECT headings.headingid
    , headings.heading
    , topics.topic
    , posts.userid
    , posts.added
    , posts.post
FROM headings
    LEFT JOIN topics ON headings.headingid = topics.headingid
    LEFT JOIN posts ON topics.topicid = posts.topicid AND concat(posts.topicid, posts.added) IN (
         SELECT concat(topicid, max(added)) AS most_recent
         FROM posts GROUP BY topicid 
    )
GROUP BY headings.headingid

[bob_the _builder]

Hi,

 

Thanks, that code generates a query error.

 

Im not sure if I should be using one query or 2?

 

Is it explained well enough for what I am trying to acheive?

 

 

 

Cheers

[bubblegum.anarchy]

Thanks, that code generates a query error.

 

Well, that is fantastic.