Jump to content

Joins


bob_the _builder

Recommended Posts

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

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.