59586

Comma separated Values

How can i fetch this query using mysql?

<strong>Table1:</strong>

id : nos 1 12,13,14 2 14 3 14,12

<strong>Table2:</strong>

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

Answer1:

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

Answer2:

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)

    <li>1</li> <li>2</li> <li>3</li> </ul>

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

      <li>1, 12</li> <li>1, 13</li> <li>1, 14</li> <li>2, 14</li> <li>3, 14</li> <li>3, 12</li> </ul>

      Answer3:

      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

      Answer4:

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

      Answer5:

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

Recommend

  • concatenating results from SQL query and NULL columns
  • Can I combine several select queries in .net so they are returned in 1 batch?
  • Rows to Columns using Sql Query
  • Wordpress plugin for dependent drop down lists
  • How can the java 'class' literal return different instances of the Class object for the sa
  • In BASH convert a string with . in float
  • Hibernate reverse engineering - mapping a table to Java enum
  • Conversion failed when converting the nvarchar value 'SELECT * FROM
  • How do you SELECT several columns with one distinct column
  • How to retrieve multiple columns from non-entity type sql query?
  • Is there a way to link a linux's thread TID and a pthread_t “thread ID”
  • Removing Duplicate Geometries
  • PDO error when wrong host name
  • Retrieving specified columns from a list of csv files to create a data data frame in R
  • How to override value that appears in a dropdown in the rails_admin gem
  • MYSQ & MVC3 SQL connection error \\ ProviderManifestToken but I am using MySQL
  • Moving mysql files across servers
  • Submit form in a displaytag pagination
  • How to check if every primary key value is being referenced as foreign key in another table
  • Adding custom controls to a full screen movie
  • Alternatives to the OPTIONAL fallback SPARQL pattern?
  • Do create extension work in single-user mode in postgres?
  • Delete MySQLi record without showing the id in the URL
  • R: gsub and capture
  • jqPlot EnhancedLegendRenderer plugin does not toggle series for Pie charts
  • Unanticipated behavior
  • SQL merge duplicate rows and join values that are different
  • Error creating VM instance in Google Compute Engine
  • Hits per day in Google Big Query
  • Trying to get generic when generic is not available
  • how does django model after text[] in postgresql [duplicate]
  • Turn off referential integrity in Derby? is it possible?
  • Add sale price programmatically to product variations
  • Reading document lines to the user (python)
  • Binding checkboxes to object values in AngularJs
  • Unable to use reactive element in my shiny app
  • Net Present Value in Excel for Grouped Recurring CF
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize
  • How to load view controller without button in storyboard?
  • How do I use LINQ to get all the Items that have a particular SubItem?