joined:Mar 12, 2007
I have a table which contains the contents of several item lists. Each row has a field `listid` and a field `itemname` and field `itemamount`. Several items will have the same listid, making them items of the same list.
Now, I want to search in all lists for a set of items and return the matches, beginning with the list that has the highest amount of matches. I do this by doing a "SELECT `listid`, SUM(`itemamount`) hits FROM items WHERE ... GROUP BY `listid` ORDER BY hits DESC". So far, so good.
But now I want to also get the list of hits for every list. The easiest way to do this would be to take the results from the query, and then doing another select which fetches all the data. The other option I found would be to use GROUP_CONCAT in the first query and then manually split the results again in my PHP code to obtain the list of items. So something like this: "SELECT `listid`, SUM(`itemamount`) hits, GROUP_CONCAT(`itemname`) FROM items WHERE ... GROUP BY `listid` ORDER BY hits DESC"
What would be the better solution? Is it more efficient to do it with GROUP_CONCAT and then do some post-parsing to get the actual data back? Or should I rather do it the other way round for clarity?