jcsuperska
asked on
Mysql group_concat() alternative in 3.23.x
I need a functional alternative to the group_concat function since its not possible for me to upgrade to 4.1 at this time.
Im creating a temporary table that stores all keyword_ids of all relevant item_ids for that site, format is item_id is PRIMARY and keyword_ids is a comma seperated list of keyword ids (eg 1,2,3). Each item has sub-properties , each sub-property has its own record in the sub-property db and is related via an item_id col. in that sub-property db, the keyword_ids col. is in the sub-property db. I need to have keyword_ids be a list of all the keyword_ids for that item, the query I have now is working fine, however its returning multiple rows for each item_id.
--------------------------
id | item_id | keyword_ids
--------------------------
1 |1 | 1,2,3
2 |1 | 1,2
3 |1 | 1
4 |1 | 4,5,6
5 |2 | 7
6 |2 | 1,4,2
7 |2 | 6,3,4
--------------------------
Im getting that by this...
CREATE TEMPORARY TABLE (id bigint(20) unsigned NOT NULL auto_increment,PRIMARY KEY (id),KEY item_id (item_id)) TYPE=MyISAM SELECT DISTINCT item_id,keyword_ids FROM my_item_joined_db WHERE site_id = x
(my_item_joined_db = few left joins that grab the particular keyword_ids for the items on that partikcular section of the site).
What I need is to have that temp table only have one row per item_id (ie GROUP BY item_id) with all the keyword_ids for that item_id concat'ed together (ie for item_id 1 keyword_ids would be 1,2,3,1,2,1,4,5,6). I need to do this all via mysql, is there a way to do it via creating another temp table and dumping from another select, etc.? Thanks.
Im creating a temporary table that stores all keyword_ids of all relevant item_ids for that site, format is item_id is PRIMARY and keyword_ids is a comma seperated list of keyword ids (eg 1,2,3). Each item has sub-properties , each sub-property has its own record in the sub-property db and is related via an item_id col. in that sub-property db, the keyword_ids col. is in the sub-property db. I need to have keyword_ids be a list of all the keyword_ids for that item, the query I have now is working fine, however its returning multiple rows for each item_id.
--------------------------
id | item_id | keyword_ids
--------------------------
1 |1 | 1,2,3
2 |1 | 1,2
3 |1 | 1
4 |1 | 4,5,6
5 |2 | 7
6 |2 | 1,4,2
7 |2 | 6,3,4
--------------------------
Im getting that by this...
CREATE TEMPORARY TABLE (id bigint(20) unsigned NOT NULL auto_increment,PRIMARY KEY (id),KEY item_id (item_id)) TYPE=MyISAM SELECT DISTINCT item_id,keyword_ids FROM my_item_joined_db WHERE site_id = x
(my_item_joined_db = few left joins that grab the particular keyword_ids for the items on that partikcular section of the site).
What I need is to have that temp table only have one row per item_id (ie GROUP BY item_id) with all the keyword_ids for that item_id concat'ed together (ie for item_id 1 keyword_ids would be 1,2,3,1,2,1,4,5,6). I need to do this all via mysql, is there a way to do it via creating another temp table and dumping from another select, etc.? Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
can you post your complete query and the error message ?
ASKER
Query is:
SELECT @prev_item_id := NULL;CREATE TEMPORARY TABLE IF NOT EXISTS keyword_table_site_2 (id bigint(20) unsigned NOT NULL auto_increment,PRIMARY KEY (id),KEY item_id (item_id)) TYPE=MyISAM SELECT items.id as item_id,MAX(@keyword_ids := IF(@prev_item_id = item_id, CONCAT_WS(',', @keyword_ids, keyword_ids), keyword_ids)),@prev_item_i
MySQL said:
Incorrect column name 'MAX(@keyword_ids := IF(@prev_item_id = item_id, CONCAT_WS(',', @keyword_ids, keyword_ids), keyword_ids))'
Thank you for your help.
SELECT @prev_item_id := NULL;CREATE TEMPORARY TABLE IF NOT EXISTS keyword_table_site_2 (id bigint(20) unsigned NOT NULL auto_increment,PRIMARY KEY (id),KEY item_id (item_id)) TYPE=MyISAM SELECT items.id as item_id,MAX(@keyword_ids := IF(@prev_item_id = item_id, CONCAT_WS(',', @keyword_ids, keyword_ids), keyword_ids)),@prev_item_i
MySQL said:
Incorrect column name 'MAX(@keyword_ids := IF(@prev_item_id = item_id, CONCAT_WS(',', @keyword_ids, keyword_ids), keyword_ids))'
Thank you for your help.
ASKER
Your solution as I thought was absolutly correct. Only issue was being caused by the fact that I was selecting the results into a TEMP table and the col names were not being aliased. So when I added as keyword_ids to the MAX(....) it worked like a charm. Always the simple things that seem to be the cause , thanks again for helping me get the group_concat() or bust thinking out of my head =), just had to get the wheels turning.
ASKER
Incorrect column name 'MAX(@keyword_ids := IF(@prev_item_id = item_id, CONCAT_WS(',', @keyword_ids, keyword_ids), keyword_ids))'
That mean Im gonna need to upgrade to 4.0.18 or anything look obviously wrong?