Tricky SQL query - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: Tricky SQL query (/showthread.php?tid=11099) Pages:
1
2
|
Tricky SQL query - El Forum - 08-26-2008 [eluser]Dan Murfitt[/eluser] Hi I was wondering if someone could offer some help with a tricky SQL query - I'm writing a messaging system which operates in a similar way to a forum. Conversations are basically forum threads, and posts are posts within a thread. What I need to be able to do is get the conversations related to a specific team, ordered by the latest updated conversations (determined by the conversation with the latest post 'created'). So, here's the schema: Conversations table: Code: CREATE TABLE `conversations` ( Code: CREATE TABLE `conversations_posts` ( Here's what I've tried so far (using example teamid 18): Code: SELECT conversations.* FROM conversations This produces a strange result - as soon as I add the GROUP BY (like above) the order becomes broken. Without the GROUP BY they are in the correct order, but there are duplicates as it shows every conversation for every post. Does anyone know how to remidy this? I just need the conversations, for a particular team, ordered by the latest 'created' date from the joined 'conversations_posts' table. I've tried lots of variations but I just can't get it working. Any help here appreciated Thanks Tricky SQL query - El Forum - 08-26-2008 [eluser]Sumon[/eluser] Hopefully meet your requirement Code: SELECT conversations.*, conversations_posts.* FROM conversations Code: SELECT conversations.*, conversations_posts.* FROM conversations Tricky SQL query - El Forum - 08-26-2008 [eluser]Dan Murfitt[/eluser] Hi Thanks for the swift reply This still produces duplicate results, as it's showing every conversation for every post - this is why I need the GROUP BY clause, so that it only returns one result for every conversationid. The problem is that the ORDER BY is performed after the GROUP BY clause. So, even though the ORDER BY is working (as the final result is ordered by the created field) the one post returned from the GROUP BY clause is incorrect. I hope this makes sense Any more ideas? Tricky SQL query - El Forum - 08-26-2008 [eluser]Sumon[/eluser] If you remove GROUP BY AND use LIMIT then what result it produce? Make sure you use ORDER BY CLAUSE (either conversations_posts.created DESC or conversations_posts.postid DESC Tricky SQL query - El Forum - 08-26-2008 [eluser]Dan Murfitt[/eluser] I need the GROUP BY so that the results only return unique conversations, otherwise it will return the conversation relating to every posts, and since there are many posts per conversations you get duplicate results. I've done a bit more searching and I seem to have found a possible solution: Code: SELECT conversations.*,COUNT(*) AS posts By doing this it seems I am able to run the order before the group as well as after. Thanks for your help Tricky SQL query - El Forum - 08-26-2008 [eluser]ehicks727[/eluser] Can you post the create with a couple lines of data for each table? I could be wrong, but I didn't think it was correct to use an ORDER BY with a JOIN. Something is just sparking a memory about that for some reason. If you post me a create with some data, I think I can probably figure this out for you. Tricky SQL query - El Forum - 08-26-2008 [eluser]ehicks727[/eluser] ah, I take that back, yes you can use ORDER BY with a JOIN. Maybe you need to specify the JOIN, like a LEFT JOIN or something like that. I didn't really read the question closely enough... haven't had my coffee yet this morning Tricky SQL query - El Forum - 08-26-2008 [eluser]Dan Murfitt[/eluser] [quote author="ehicks727" date="1219776513"]ah, I take that back, yes you can use ORDER BY with a JOIN. Maybe you need to specify the JOIN, like a LEFT JOIN or something like that. I didn't really read the question closely enough... haven't had my coffee yet this morning [/quote] Thanks for your help I think it was the group by which I am using incorrectly, as I'm using it to only show one result for each conversation. Here's an SQL dump from phpmyadmin which contains some sample data. This contains 4 conversations, which were created in order as their topic will suggest, but conversationid 2 has 2 posts. The second post in conversationid 2 was the last to be updated, so it should appear at the top of the resultset making the order 2, 4, 3, 1 (in accordance with the conversations_posts table, ordered by the created field). If I only created a join (but no group by) there would be 2 results for conversationid 2, as there are two posts - so to limit this to 1 result per conversation I group the conversationid. Here's the SQL: Code: -- phpMyAdmin SQL Dump Thanks again! Tricky SQL query - El Forum - 08-26-2008 [eluser]mdowns[/eluser] Why not add a column to the conversations table called LastPost which would be a datetime? Each time you insert a row into the conversations_posts table, update the LastPost date in the conversation table (either manually or by creating a trigger). That would make the query a lot easier. Tricky SQL query - El Forum - 08-26-2008 [eluser]ehicks727[/eluser] Does this producing the result you expect? Or is it the same as the WHERE... GROUP BY Code: SELECT conversations.* FROM conversations |