Comma separated Values

Question

How can i fetch this query using mysql?

Table1:

id : nos

1   12,13,14
2   14
3   14,12

Table2:

id : values

12   PHP
13   JAVA
14   C++

Now , I want output like this:

1   PHP, JAVA, C++
2   C++
3   C++, PHP

Answer 1

Not tested but it should be something like this:

SELECT table1.id, GROUP_CONCAT(table2.values)
FROM table1 INNER JOIN table2 ON FIND_IN_SET(table2.id, table1.nos)
GROUP BY table1.id

Answer 2

There's no way that I know of to achieve that in SQL. You should instead have a 1 to N relationship to represent those lists. Something like:

Table 1: (just ids)

• 1
• 2
• 3

Table 1.1: (map ids to values in their list)

• 1, 12
• 1, 13
• 1, 14
• 2, 14
• 3, 14
• 3, 12

Answer 3

Intended this as comment but it is getting long.

SoulMerge answer(+1) is specific to MySql, which the question was intially intended. Please see the edits for the initial question.

Seems the question again got edited for the MY-SQL, but anyway.

While you can achieve this in MS SQL by using PATINDEX, I am not sure you can do it this in oracle.

I think it would be better to restructure the tables as suggested by jo227o-da-silva(+1).

Answer 4

Although not completely relevant to the subject (MySQL), but will help others finding the question by title, in MSSQL server this can be achived using the FOR XML hint and some nasty string replacements.

I'll post up some code when I find it...

Answer 5

Not sure if this will work in mySQL but in SqlServer you could create a function:

create function dbo.replaceIdsWithValues
(
    @inIds varchar(50)
)
returns varchar(50)
as
begin
    declare @ret as varchar(50)
    set @ret = @inIds
    select @ret = replace(@ret,cast(id as varchar),theValues) from t2
    return @ret
end

and then simply call:

select id, nos, dbo.replaceIdsWithValues(nos) from t1

that assuming your tables structure:

create table t1 (id int, nos varchar(50))
create table t2 (id int, theValues varchar(50))

You can test the full example

create table t1 (id int, nos varchar(50))
create table t2 (id int, theValues varchar(50))
insert into t1(id, nos)
select 1, '12,13,14'
union all select 2, '14'
union all select 3, '14,12'

insert into t2(id, theValues)
select 12, 'PHP'
union all select 13, 'JAVA'
union all select 14, 'C++'

select id, nos, dbo.replaceIdsWithValues(nos) from t1

Answer 6

i used like this as soulmerge said,

SELECT table1.id, GROUP_CONCAT(table2.values) FROM table1 INNER JOIN table2 ON FIND_IN_SET(table2.id, table1.nos) GROUP BY table1.id

But i have another field nos1 ( with multiple values) in table1 that also pointing to table2, i used like this :

SELECT table1.id, GROUP_CONCAT(table2.values),GROUP_CONCAT(table2.values) FROM table1 INNER JOIN table2 ON FIND_IN_SET(table2.id, table1.nos), INNER JOIN table2 ON FIND_IN_SET(table2.id, table1.nos1), GROUP BY table1.id

But it is not working proberly. i am not getting how to use n this situation.

Here is my detailed explanation :

Table 1:

id     nos       nos1 1     12, 13, 14       15,16   2     14       16   3     14,12       15

Table 2 :

id   values 12   PHP 13   JAVA 14   C++ 15   mysql 16   mysql server

Now , I want output like this:

**id**       **nos**         **strong text**nos1 1       PHP, JAVA, C++         mysql,sqlserver 2       C++         mysql 3       C++, PHP         sqlserver

Please let me know how to write query for this.

Thanks in advance