CODEWITHSUNDEEP
sqlmysql
Gnaniyar
Gnaniyar

Reputation:

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, JAVA, C++
2   C++
3   C++, PHP

Upvotes: 2

Views: 733

Answers (5)

kristof
kristof

Reputation: 53824

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

Upvotes: 1

cjk
cjk

Reputation: 46415

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

Upvotes: 0

Biswanath
Biswanath

Reputation: 9185

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

Upvotes: 0

Joao da Silva
Joao da Silva

Reputation: 7639

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

Upvotes: 3

soulmerge
soulmerge

Reputation: 75704

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

Upvotes: 9

Related Questions