jonniejoejonson Posted September 10, 2009 Share Posted September 10, 2009 I have mutliple tables. The following sql attempts to select all the forums from the forum table. It also selects all the categories for each selected forum, from the categroies table. It also selects the most recent post in each selected category from the posts table. It also selects the user info for the most recent poster. $sql = "SELECT a.*, b.*, c.maxPostId, d.*, e.* FROM forums AS a LEFT JOIN categories AS b ON b.forumId=a.forumId LEFT JOIN (SELECT categoryId, MAX(postId) AS maxPostId FROM posts) AS c ON c.categoryId=b.categoryId LEFT JOIN posts AS d ON c.maxPostId=d.postId LEFT JOIN users AS e ON e.userId=d.userId "; problem.. The sql works fine except it is only pulling the most recent post regardless of categoryId. It is also only selecting one post in total. I hope you understand.. thanks to any responders. Quote Link to comment https://forums.phpfreaks.com/topic/173774-solved-complex-select-thursday-teaser/ Share on other sites More sharing options...
artacus Posted September 10, 2009 Share Posted September 10, 2009 Your subquery should be grouped by category_id. Most other db's wouldn't let you run that subquery for that reason. Also, I would recommend giving your tables useful aliases and not a, b, c. Quote Link to comment https://forums.phpfreaks.com/topic/173774-solved-complex-select-thursday-teaser/#findComment-916141 Share on other sites More sharing options...
jonniejoejonson Posted September 10, 2009 Author Share Posted September 10, 2009 artacus... thanks very much... it must be a wanderfull thing to be as brainy as you and the one they call keith... much appreciated. Quote Link to comment https://forums.phpfreaks.com/topic/173774-solved-complex-select-thursday-teaser/#findComment-916213 Share on other sites More sharing options...
artacus Posted September 10, 2009 Share Posted September 10, 2009 it must be a wanderfull thing to be as brainy as you and the one they call keith. LOL. On the contrary, suicide rates are MUCH higher in the gifted and genius populations. I guess lending some credibility to the phrase "ignorance is bliss." Besides, I'm sure it has more to do with 15 years of experience than braininess. Quote Link to comment https://forums.phpfreaks.com/topic/173774-solved-complex-select-thursday-teaser/#findComment-916243 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.