Solved

Mysql group_concat() alternative in 3.23.x

Posted on 2004-04-15
5
2,409 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:jcsuperska
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 14

Accepted Solution

by:
psadac earned 500 total points
ID: 10847100
It works well on mysql 4.0.18 (it should work with 3.X versions also) :

SELECT @prev_item_id := NULL;

SELECT  MAX(@keyword_ids := IF(@prev_item_id = item_id, CONCAT_WS(',', @keyword_ids, keyword_ids), keyword_ids)),
             @prev_item_id := item_id
FROM mytable
GROUP BY item_id;

very standard sql, no ? ;-)
0
 

Author Comment

by:jcsuperska
ID: 10861896
That sounds like it should work great, unfortunatly mysql is returning this error and any help with this would be greatly appreciated.

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?
0
 
LVL 14

Expert Comment

by:psadac
ID: 10863258
can you post your complete query and the error message ?
0
 

Author Comment

by:jcsuperska
ID: 10863456
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_id := item_id FROM sub_items LEFT JOIN items ON sub_items.item_id = items.id WHERE items.site_id = 2 AND items.site_date <= NOW() GROUP BY item_id

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.
0
 

Author Comment

by:jcsuperska
ID: 10864666
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.
0

Featured Post

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question