verid verid - 6 months ago 17
SQL Question

SQL query syntax to map a list

I'm a newbie at SQL; this is for a volunteer project. I know the basics and have had experience in other programming languages, but haven't done any for a long time. I have looked for the answer to this myself but I haven't been able to translate anything I've found into what I want to do.

Am using mysql.

I have a table1 with data list this:

id list list2
1 2,3,4 4
2 1,3 5,2
3 2,4,5 1
4 3 4


and another table2 like this

list nlist
1 5
2 3
3 4
4 1
5 2


This statement

select table1.id, table1.list, table2.nlist, table1.list2
from table1
join table2 on table1.list=table2.list;


results in:

id list nlist list2
1 2,3,4 3 4
2 1,3 5 5,2
3 2,4,5 3 1
4 3 4 4


But I need to generate a result like this:

id list nlist list2
1 2,3,4 3,4,1 4
2 1,3 5,4 5,2
3 2,4,5 3,1,2 1
4 3 4 4


Essentially, mapping nlist into the original table. (the id field is an auto-generated key the list/nlist/list2 field are varchars.) (all the values in the list/nlist/list2 columns are integers or comma-delimited lists of integers.) (oh, and I did not design the data structure!)

I am thinking that instead of 'table2.nlist' in the first part of the select statement, I need to put a function there, presumably a loop/map type function? Is this right or am I lost again?
(I then need to extract the rows where nlist contains a number that list2 doesn't, but I haven't got to that yet.)

EDIT

Thank you valex! This is what ended up giving me what I needed:

SELECT t1.id,
t1.list,
GROUP_CONCAT(t2.nlist) nlist,
GROUP_CONCAT(t2.list2) list2
FROM table1 t1 JOIN table2 t2
WHERE FIND_IN_SET(t2.list,t1.list) IS TRUE
GROUP BY t1.id;

Answer

Try to use FIND_IN_SET to join table and then GROUP_CONCAT to form nlist field:

SELECT t1.id,
       MAX(t1.list) list,
       GROUP_CONCAT(t2.newlist) nlist,
       MAX(t1.list2) list2
FROM Table1 t1
JOIN Table2 t2 on FIND_IN_SET(t2.id,t1.list) 
GROUP BY t1.id