CODEWITHSUNDEEP
mysqlsql
verid
verid

Reputation: 43

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, 
       t2.list2 
FROM table1 t1 JOIN table2 t2 
WHERE FIND_IN_SET(t2.list,t1.list) IS TRUE 
GROUP BY t1.id;

Upvotes: 4

Views: 2902

Answers (1)

valex
valex

Reputation: 24144

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

Upvotes: 2

Related Questions