smerny Posted June 16, 2009 Share Posted June 16, 2009 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... Link to comment Share on other sites More sharing options...
justsomeguy Posted June 16, 2009 Share Posted June 16, 2009 The select query does not have an AND in it, you just list out all the column names, then you have a FROM clause where you list which tables to join. Link to comment Share on other sites More sharing options...
smerny Posted June 16, 2009 Author Share Posted June 16, 2009 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? Link to comment Share on other sites More sharing options...
smerny Posted June 16, 2009 Author Share Posted June 16, 2009 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted June 16, 2009 Share Posted June 16, 2009 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 More sharing options...
smerny Posted June 17, 2009 Author Share Posted June 17, 2009 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 More sharing options...
justsomeguy Posted June 17, 2009 Share Posted June 17, 2009 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 More sharing options...
smerny Posted June 17, 2009 Author Share Posted June 17, 2009 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 More sharing options...
smerny Posted June 17, 2009 Author Share Posted June 17, 2009 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 Link to comment Share on other sites More sharing options...
smerny Posted June 17, 2009 Author Share Posted June 17, 2009 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 More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.