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?](http://stackoverflow.com/a/3653574) — eggyal, Jun 11 '13 at 15:31
Is the data in table 3 literally stored as `"{1:2}"` (5 characters)? — gen_Eric, Jun 11 '13 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: http://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, Jun 11 '13 at 15:35
The thing is that I am not allowed to touch the structure of the database, I just have to write a query that gives me that result — sm13294, Jun 11 '13 at 15:40

Kickstart · Accepted Answer · 2013-06-11T16:35:33.020

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 '13 at 16:35

answered Jun 11 '13 at 15:43

Kickstart

21,403
2
21
33

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 – sm13294 Jun 11 '13 at 15:54
You can add a DISTINCT clause to the GROUP_CONCAT which should fix that (if you mean remove the duplicates). – Kickstart Jun 11 '13 at 15:56
1

I'd *highly* suggest you group by the contact's ID instead of their name! – gen_Eric Jun 11 '13 at 16:19
Fully agree for real use (and amending answer to go with this) – Kickstart Jun 11 '13 at 16:33

mysql join, replace for comma separated strings

1 Answers1