mysql join, replace for comma separated strings

Question

Database:

table1 schema: name=q_values;  columns= id, name;
table2 schema: name=signup_protect;  columns= first_name, last_name, uid;
table3 schema: name=user_result;  columns= uid, value_ids;

table1 data: [1, forest], [2, mountains]
table2 data: [test, test, 123]
table 3 data: [123, {1:2}]

I will need the query that gives me this result:

test, test, {forest, mountains}

which means that I join signup with result on uid, and I replace the value_ids with q_value names.

This is what I did so far, but I only get the first name of the comaseparated value.

SELECT `signup_protect`.`first_name`, `signup_protect`.`last_name`, 
 (select name from q_values where id in (replace(`user_results`.`value_ids`,':', ','))) 
 FROM (`signup_protect`) JOIN `user_results` ON `user_results`.`uid` = `signup_protect`.`uid`

I use

(select name from q_values where id in (replace(`user_results`.`value_ids`,':', ',')))

to replace the colons on my string with comma so I can treat them as array .

See Is storing a delimited list in a database column really that bad? — eggyal
– eggyal, Commented Jun 11, 2013 at 15:31
Is the data in table 3 literally stored as "{1:2}" (5 characters)? — gen_Eric
– gen_Eric, Commented Jun 11, 2013 at 15:33
The problem is that IN expects a comma separated list, but you're giving it a string. Have a look at this question of mine: stackoverflow.com/q/4155873/206403 and please consider not storing a (comma) separated list in a database, you should have one row for each value (do it, you'll thank me later on). — gen_Eric
– gen_Eric, Commented Jun 11, 2013 at 15:35

Kickstart · Accepted Answer · 2013-06-11 16:35:33Z

2

I would also suggest normalising the design of the database.

However failing that it is possible a couple of ways. For example something like this (not tested):-

SELECT a.first_name, a.last_name, GROUP_CONCAT(DISTINCT c.name)
FROM signup_protect a
INNER JOIN user_results b 
ON b.uid = a.uid
INNER JOIN q_values c
ON FIND_IN_SET(c.id, REPLACE(b.value_ids, ':', ',')) > 0
GROUP BY a.first_name, a.last_name

Don't expect it to be quick!

As prompted by Rocket Hazmat it is best to GROUP BY a real unique value (names might not be unique) hence something like this:-

SELECT a.uid, a.first_name, a.last_name, GROUP_CONCAT(DISTINCT c.name)
FROM signup_protect a
INNER JOIN user_results b 
ON b.uid = a.uid
INNER JOIN q_values c
ON FIND_IN_SET(c.id, REPLACE(b.value_ids, ':', ',')) > 0
GROUP BY a.uid

edited Jun 11, 2013 at 16:35

answered Jun 11, 2013 at 15:43

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sm13294 Over a year ago

This one works, but when there are more than one records, I dont get the c.name only from value_ids but I get more than that example: for these ids: 2:23:42:45:57:76:82:92, i get all these names: Mountains,Wetlands,Wetlands,New Orleans,Eggs,Rice,Rice,Fresh Veggies,Peaches,Peaches,Chocolate,Pie,Wine,Coffee,Coffee,Tea,Hockey,Hockey,Running,Running,Outdoor Music,Outdoor Music,Travel,Photography,Peace,Peace,Community,Community,Friends,Friends,Birds,Horses

Kickstart Over a year ago

You can add a DISTINCT clause to the GROUP_CONCAT which should fix that (if you mean remove the duplicates).

gen_Eric Over a year ago

I'd highly suggest you group by the contact's ID instead of their name!

Kickstart Over a year ago

Fully agree for real use (and amending answer to go with this)

Collectives™ on Stack Overflow

mysql join, replace for comma separated strings

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related