Jump to content

Join Tables Question


smerny
 Share

Recommended Posts

I have a table "topics" with the columns:ID, ID_board, name, description, views, order, lockedand then a table "posts" with the columns:ID, ID_topic, ID_board, ID_poster, posttime, modifier, subject, body, modifiedtime===================What I want to do is create a query thatSELECTStopics.name, topics.description, topics.views, topics.order, topics.lockedANDposts.posttime (but ONLY on the post that has "posts.ID_topic = $id_topic" with the latest posts.posttime)WHEREtopics.ID_board = $id_boardORDER BYposts.posttime DESC=====================Hope that is clear enough...

Edited by smerny
Link to comment
Share on other sites

If you know of how to do this, can you show me an example?The thing I have trouble with is "but ONLY on the post that has "posts.ID_topic = $id_topic" with the latest posts.posttime"Maybe the only way is to have a separate query get SELECT posttime FROM posts WHERE ID_topic='".$topics['ID']."' ORDER BY posttime DESC LIMIT 1for within the loop for each topic... but then how would I sort it by posttime?

Edited by smerny
Link to comment
Share on other sites

SELECTtopics.name, topics.description, topics.views, topics.order, topics.locked, posts.posttime (WHERE "posts.ID_topic = $id_topic" ORDER BY posts.posttime DESC LIMIT 1)WHEREtopics.ID_board = $id_boardORDER BYposts.posttime DESC============that is what I want, I just don't know the syntax for it... or if there is one..(the red text being what i am not sure how to go about)and also the INNER JOIN or LEFT or whatever, still a little unsure how to fit that in all this as well

Edited by smerny
Link to comment
Share on other sites

You're still missing a FROM. All of the conditions you want to filter on go in the WHERE clause, not in the field list.SELECTtopics.name, topics.description, topics.views, topics.order, topics.locked, posts.posttime FROM topics, postsWHEREtopics.ID_board = $id_boardAND post.ID_topic = $id_topicORDER BYposts.posttime DESCIt doesn't look like you're joining the tables on anything. That means you're going to get all possible combinations of rows that satisfy the WHERE conditions, i.e. all possible combinations of rows that have a certain ID from topics.ID_board and post.ID_topic.

Link to comment
Share on other sites

but the thing is, i can't do "$id_topic" because the topics are going to be looping to display them... and I only want the time of the most recent post within each topicwithin this loop i need to display information about the topic and the time and author of the most recent post within that topic

Link to comment
Share on other sites

You need to use a left join then, so that you get all topics, and join each topic with the post to get the most recent post. You would probably need to use MAX and GROUP BY for that.SELECTtopics.name, topics.description, topics.views, topics.order, topics.locked, MAX(posts.posttime) AS posttime, posts.author, posts.id FROM topics LEFT JOIN posts ON topics.ID = posts.ID_topicWHERE topics.ID_board=$id_boardGROUP BY posts.ID_topicORDER BY posttime DESCI haven't tested that, but look into left joins and using group by for more information.

Link to comment
Share on other sites

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY posts.ID_topic ORDER BY posttime DESC' at line 3

Link to comment
Share on other sites

SELECT topics.ID, topics.name, topics.description, topics.views, topics.order, topics.locked, postSub.LatestPostTimeFROM topicsLEFT OUTER JOIN (SELECT ID_board, MAX(posttime) AS LatestPostTime FROM posts GROUP BY ID_board) postSubON topics.ID_board = postSub.ID_boardORDER BY postSub.LatestPostTime DESC

this is sorting in the right order, but i can't get it to limit the topics to the ones that have ID_board the same as $id_board

Edited by smerny
Link to comment
Share on other sites

Got it, if anyone is interested....

SELECT topics.ID, topics.name, topics.description, topics.views, topics.order, topics.locked, postSub.LatestPostTimeFROM topicsLEFT OUTER JOIN (SELECT ID_board, MAX(posttime) AS LatestPostTime FROM posts GROUP BY ID_board) postSubON topics.ID_board = postSub.ID_boardWHERE topics.ID_board='$board_id'ORDER BY postSub.LatestPostTime DESC

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...