npsari Posted February 8, 2008 Share Posted February 8, 2008 Hi q = "SELECT DISTINCT t.topic_id, t.topic_name, t.topic_category_id, t.topic_date, t.topic_time, u.Image1, m.category_name, u.ID, r.reply_date, r.reply_time FROM topic t LEFT JOIN profile u ON u.ID = t.topic_who LEFT JOIN category m ON t.topic_category_id = m.category_id LEFT JOIN reply r ON t.topic_id = r.reply_topic_id ORDER BY t.topic_date DESC, t.topic_time DESC LIMIT 9;"; The above code does not display distinct topic_id it gets repeated many times Can you tell me how to fix it please Quote Link to comment Share on other sites More sharing options...
Barand Posted February 8, 2008 Share Posted February 8, 2008 DISTINCT refers to the whole row - you get unique combinations of row contents. If you are joining table and have multiple matching records then you are going to get the id repeated for each matching row. One way is to use GROUP BY with GROUP_CONCAT() Quote Link to comment Share on other sites More sharing options...
npsari Posted February 8, 2008 Author Share Posted February 8, 2008 I added the GROUP BY, it worked and thinsga re not repeated, but now, the topics are not ordered by the latest reply (r.reply_date, r.reply_time) $q = "SELECT DISTINCT t.topic_id, t.topic_name, t.topic_category_id, t.topic_date, t.topic_time, u.Image1, m.category_name, u.ID, r.reply_date, r.reply_time FROM topic t LEFT JOIN profile u ON u.ID = t.topic_who LEFT JOIN category m ON t.topic_category_id = m.category_id LEFT JOIN reply r ON t.topic_id = r.reply_topic_id GROUP BY t.topic_id ORDER BY r.reply_date DESC, r.reply_time DESC LIMIT 9;"; Any other hint plz Quote Link to comment Share on other sites More sharing options...
Barand Posted February 8, 2008 Share Posted February 8, 2008 You haven't tried all the first hint yet Quote Link to comment Share on other sites More sharing options...
npsari Posted February 8, 2008 Author Share Posted February 8, 2008 can you lead me the way please Quote Link to comment Share on other sites More sharing options...
npsari Posted February 8, 2008 Author Share Posted February 8, 2008 if it is hard, thats fine i think i know what the problem In the table reply reply_topic_id is repeated many times for each topic, that is why the query is selecting any reply_date Quote Link to comment Share on other sites More sharing options...
Barand Posted February 8, 2008 Share Posted February 8, 2008 Not tested - I don't have your data <?php $q = "SELECT t.topic_id, t.topic_name, t.topic_category_id, t.topic_date, t.topic_time, u.Image1, m.category_name, u.ID, GROUP_CONCAT(r.reply_date, ' ', r.reply_time ORDER BY r.reply_date DESC,r.reply_time DESC SEPARATOR '<br/>') as replies FROM topic t LEFT JOIN profile u ON u.ID = t.topic_who LEFT JOIN category m ON t.topic_category_id = m.category_id LEFT JOIN reply r ON t.topic_id = r.reply_topic_id GROUP BY t.topic_id"; Quote Link to comment Share on other sites More sharing options...
npsari Posted February 8, 2008 Author Share Posted February 8, 2008 wow, cool ill give it a try thanks for the help barand Quote Link to comment 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.