Mysql, exchange comma separated values by internal select with where clause

Question

I have 2 tables:

tab1 (value has comma separated ids from tab2):

id1   val1
-----------
1      1
2     1,2
3     1,3

tab2:

id2   val2
-----------
1      a
2      b
3      c

I want to list values from Tab1 and replace comma separated val1 with val2

I made something like this:

SELECT *, (SELECT GROUP_CONCAT(val2) FROM tab2 WHERE id2 IN val1 ) from tab1

but it generates syntax error near 'val1 ) from tab1

When I remove Where Clause it works perfect:

SELECT *, (SELECT GROUP_CONCAT(val2) FROM tab2) from tab1

and it produces:

id1   val1  (SELECT GROUP_CONCAT(val2) FROM tab2 )
1     1,2      a,b,c
2     1,3      a,b,c

but I want to have in 3rd row letters that corresponds with Val1 numbers (only a,b and a,c). The Key is to replace numbers in Tab1.val1: 1,2... with letters from tab2.val2. It should produce:

id1   val1  (SELECT GROUP_CONCAT(val2) FROM tab2 WHERE id2 IN val1)
1     1,2      a,b
2     1,3      a,c

Something is happening when I add where clause but I cannot find what is my mistake.

Maybe some other idea to replace this comma separated numbers with letter values from other table?

Don't save CSV in a column http://stackoverflow.com/questions/41304945/best-type-of-indexing-when-there-is-like-clause/41305027#41305027 http://stackoverflow.com/questions/41215624/sql-table-with-list-entry-vs-sql-table-with-a-row-for-each-entry/41215681#41215681 — e4c5, Jan 26 '17 at 08:55
no. third column should corespond to second but replace 1,2 with letters from tab2. If Val1= 1,1,2,2,3,3,3,3 then in third column it should apear: a,a,b,b,c,c,c,c — Barto, Jan 26 '17 at 09:07
Tab1.Val1 has keys form Tab2.id2, I want to replace them with Tab2.Val2 — Barto, Jan 26 '17 at 09:09
This is tagged mysql, but until you fix the data set to 3NF it ain't sql at all. — Strawberry, Jan 26 '17 at 09:37

score 2 · Accepted Answer · answered Jan 26 '17 at 13:47

2

You can use the FIND_IN_SET() function:

SELECT *, (SELECT GROUP_CONCAT(val2) FROM `tab2` WHERE FIND_IN_SET(id2,val1) > 0 ) as val2_tab2 FROM `tab1` ;

Here is a fiddle: http://rextester.com/ZMFML52150

answered Jan 26 '17 at 13:47

moni_dragu

1,163
9
16

This is Great! Thank You very much:) – Barto Jan 26 '17 at 18:17

P.Salmon · Answer 2 · 2017-01-26T10:00:39.237

If you can create a function this might do

drop function if exists `F`;
delimiter //

CREATE DEFINER=`root`@`localhost` FUNCTION `F`(
    `instring` varchar(255)
)
RETURNS varchar(255) CHARSET latin1
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
begin
declare   tempstring varchar(100);
declare   outstring  varchar(100);
declare  val2string varchar(100);
declare  checkit int;
set tempstring = ltrim(rtrim(instring));
set checkit = 0;
set val2string = '';
looper: while   tempstring is not null and instr(tempstring,',') > 0 do
        set outstring = substr(tempstring,1,instr(tempstring, ',') - 1);
        set tempstring = substr(tempstring,instr(tempstring, ',') + 1,length(tempstring) - instr(tempstring, ',') + 1);
        set checkit = 1 ;
        set val2string = concat(val2string,(select val2 from tab2 where id = outstring),',');
end while; 


if checkit = 0 then 
    set val2string = 'Comma not found-no val2'; 
else
    set val2string = concat(val2string,(select val2 from tab2 where id = tempstring));
end if;
return val2string;
end //

delimiter ;

ariaDB [sandbox]> drop table if exists tab1;
Query OK, 0 rows affected (0.09 sec)

MariaDB [sandbox]> drop table if exists tab2;
Query OK, 0 rows affected (0.08 sec)

MariaDB [sandbox]> create table tab1(id int,  val1 varchar(20));
Query OK, 0 rows affected (0.22 sec)

MariaDB [sandbox]> insert into tab1 values
    -> (1  ,    '1'),
    -> (2  ,   '1,2'),
    -> (3  ,   '1,3'),
    -> (4  ,   '1,1,2,2,3,3,3,3');
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> create table tab2(id int,  val2 varchar(20));
Query OK, 0 rows affected (0.30 sec)

MariaDB [sandbox]> insert into tab2 values
    -> (1 ,     'a'),
    -> (2 ,     'b'),
    -> (3 ,     'c');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> SELECT ID,`F`(VAL1) FROM TAB1;
+------+-------------------------+
| ID   | `F`(VAL1)               |
+------+-------------------------+
|    1 | Comma not found-no val2 |
|    2 | a,b                     |
|    3 | a,c                     |
|    4 | a,a,b,b,c,c,c,c         |
+------+-------------------------+
4 rows in set (0.00 sec)

Mysql, exchange comma separated values by internal select with where clause

2 Answers2