kirk112 Posted October 2, 2007 Share Posted October 2, 2007 Hi, I am trying to group fields together, which I have managed using GROUP_CONCAT, but when I concat on a SMALLINT datatype the results is return as ls_id locations 1 [bLOB - 2 Bytes] 2 [bLOB - 2 Bytes] instead of ls_id locations 1 '1', '2' 2 '1', '3' This is the query that I have been using. SELECT ls_id, GROUP_CONCAT(DISTINCT(ls_id), '\'' SEPARATOR ',\'') AS locations FROM ip_locations AS ip LEFT JOIN location_specific AS ls ON ls.specific_id = ip.ls_id WHERE ip.ip_code IN ('A1', 'A2', 'A3', 'A4') GROUP BY ls_id Does anyone know where I am going wrong? Thanks for your help! Quote Link to comment Share on other sites More sharing options...
effigy Posted October 2, 2007 Share Posted October 2, 2007 I don't think DISTINCT can be used in that manner. What if you move it after SELECT? Quote Link to comment Share on other sites More sharing options...
kirk112 Posted October 2, 2007 Author Share Posted October 2, 2007 Hi effigy If I take out the distinct altogether I still have the same problem, I.E. ls_id locations 1 [bLOB - 10 Bytes] 2 [bLOB - 2 Bytes] Cheers Quote Link to comment Share on other sites More sharing options...
fenway Posted October 2, 2007 Share Posted October 2, 2007 I don't think DISTINCT can be used in that manner. What if you move it after SELECT? Acutally, it is valid that way... but try removing all parameters to GROUP_CONCAT(). Quote Link to comment Share on other sites More sharing options...
kirk112 Posted October 2, 2007 Author Share Posted October 2, 2007 I have taken it all the way back to SELECT GROUP_CONCAT(ls_id) FROM ip_locations AS ip WHERE ip.ip_code IN ('A1', 'A2', 'A3', 'A4') GROUP BY ls_id And I am still getting the same blob result Quote Link to comment Share on other sites More sharing options...
fenway Posted October 2, 2007 Share Posted October 2, 2007 Then there's something wrong with the way php is handling this... that's php output, not mysql output. Quote Link to comment Share on other sites More sharing options...
Barand Posted October 2, 2007 Share Posted October 2, 2007 GROUP_CONCAT(ls_id) and you GROUP BY ls_id ? ??? ??? Quote Link to comment Share on other sites More sharing options...
kirk112 Posted October 2, 2007 Author Share Posted October 2, 2007 Hi Barand I have removed the GROUP BY ls_id and I am still getting the same incorrect results I have been running this query in phpMyAdmin. If I run this query and replace GROUP_CONCAT(ls_id) to a varchar field (instead of an smallint) it run correctly, does anyone have any idea on where to start debugging this I have been going around in circles for days Thanks! Quote Link to comment Share on other sites More sharing options...
Barand Posted October 2, 2007 Share Posted October 2, 2007 Don't you want to group locations for each ls_id? SELECT ls.ls_id, GROUP_CONCAT(DISTINCT ip.location SEPARATOR ',') as locations FROM ip_locations AS ip LEFT JOIN location_specific AS ls ON ls.specific_id = ip.ls_id WHERE ip.ip_code IN ('A1', 'A2', 'A3', 'A4') GROUP BY ls.ls_id Quote Link to comment Share on other sites More sharing options...
kirk112 Posted October 2, 2007 Author Share Posted October 2, 2007 No idea why this works but if I change the original query to SELECT ls_id, CONCAT('\'', GROUP_CONCAT(DISTINCT(ls_id), '\'' SEPARATOR ',\'')) AS locations FROM ip_locations AS ip LEFT JOIN location_specific AS ls ON ls.specific_id = ip.ls_id WHERE ip.ip_code IN ('A1', 'A2', 'A3', 'A4') GROUP BY ls_id it works perfect, but a soon as I remove the first concat, it reverts back to giving me all the results as a blob. Any ideas why?? Thanks Barand for your help, got myself mixed up with the fields that I wanted to select, should be able to work it out after I have gotten some sleep. Thanks again everyone! Quote Link to comment Share on other sites More sharing options...
fenway Posted October 3, 2007 Share Posted October 3, 2007 Ah yes, I forgot about that bug... came across that one a long time ago while using an ODBC driver using an older version of the connector (not that I can find any refererence to it anymore). Apparently, it gets confused and "forgets" to stringify it. Quote Link to comment Share on other sites More sharing options...
kirk112 Posted October 3, 2007 Author Share Posted October 3, 2007 Hi Fenway, thanks for your response explained a lot. Just one more thing when using group_concat it always retrieves one row even if the where does not match any fields, I can over come this by using 'having length' but this seems a little bit messy, just wondering if there is a better way to achieve this? SELECT CONCAT('\'',GROUP_CONCAT(DISTINCT(ls_id), '\'' SEPARATOR ',\'')) AS locations FROM ip_locations AS ip WHERE ip.ip_code = 'jkj' HAVING length(locations) > 0 Again thanks for your time Quote Link to comment Share on other sites More sharing options...
fenway Posted October 3, 2007 Share Posted October 3, 2007 What do you mean? If there's no match, you shouldn't get any rows back, group_concat() or not... Quote Link to comment Share on other sites More sharing options...
kirk112 Posted October 3, 2007 Author Share Posted October 3, 2007 Everytime there is no match I still get a NULL row returned???? Quote Link to comment Share on other sites More sharing options...
fenway Posted October 3, 2007 Share Posted October 3, 2007 Everytime there is no match I still get a NULL row returned???? NULL row? From just a single table? Sounds implausible... and select * doesn't give you a null row? Quote Link to comment Share on other sites More sharing options...
kirk112 Posted October 3, 2007 Author Share Posted October 3, 2007 Hi fenway, I have attached a screen shot of this, I really don't understand why a NULL row is returned every time, if I run select * it returns an empty data set. [attachment deleted by admin] Quote Link to comment Share on other sites More sharing options...
fenway Posted October 4, 2007 Share Posted October 4, 2007 Hmmm... maybe all aggregate function always return a single row, like count, sum, avg, etc... and group_concat behaves like one of these too! 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.