How can i fetch this query using mysql?
id : nos 1 12,13,14 2 14 3 14,12
id : values 12 PHP 13 JAVA 14 C++
<strong>Now , I want output like this:</strong>
1 PHP, JAVA, C++ 2 C++ 3 C++, PHP
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
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)
Table 1.1: (map ids to values in their list)
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
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).
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...