Comma Separated Values

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,

Solution 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)
GROUPBY table1.id

Solution 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

Solution 3:

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

createfunction dbo.replaceIdsWithValues
(
    @inIdsvarchar(50)
)
returnsvarchar(50)
asbegindeclare@retasvarchar(50)
    set@ret=@inIdsselect@ret= replace(@ret,cast(id asvarchar),theValues) from t2
    return@retend

and then simply call:

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

that assuming your tables structure:

createtable t1 (id int, nos varchar(50))
createtable t2 (id int, theValues varchar(50))

You can test the full example

createtable t1 (id int, nos varchar(50))
createtable t2 (id int, theValues varchar(50))
insertinto t1(id, nos)
select1, '12,13,14'unionallselect2, '14'unionallselect3, '14,12'insertinto t2(id, theValues)
select12, 'PHP'unionallselect13, 'JAVA'unionallselect14, 'C++'select id, nos, dbo.replaceIdsWithValues(nos) from t1 

Solution 4:

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).

Solution 5:

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...