Concatenate a table based on comma separated values

How can I join two tables where one of the tables has multiple comma separated values ​​in one column that are referenced id

in the other column?

1st table

Name    | Course Id
====================
Zishan  | 1,2,3                                           
Ellen   | 2,3,4                

      

Second table

course id | course name 
=======================
   1      |  java
   2      |  C++
   3      |  oracle
   4      |  dot net

      

+3
sql-server


source to share


4 answers


Perhaps this is ugliness, I haven't checked the results:



select names.name, courses.course_name
from names inner join courses
    on ',' + names.course_ids + ',' like '%,' + cast(courses.course_id as nvarchar(20)) + ',%'

      

+11


source to share


First of all, your database structure is not normalized and should have been. Since it is already configured this way, here's how to fix the problem.

You will need a function to split the string first:

CREATE FUNCTION SPLIT_STRING(str VARCHAR(255), delim VARCHAR(12), pos INT) RETURNS VARCHAR(255)

     RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),
            LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1), delim, '');

      

Then you need to create a view to complete your structure:

CREATE VIEW database.viewname AS 
    SELECT SPLIT_STRING(CourseID, ',', n) as firstField,
           SPLIT_STRING(CourseID, ',', n) as secondField,
           SPLIT_STRING(CourseID, ',',n) as thirdField 
    FROM 1stTable;

      

Where n is the nth element in your list.



Now that you have a view that generates your split fields, you can make a normal join in your view, just use your view as if you were using a table.

SELECT * 
FROM yourView 
JOIN table1.field ON table2.field

      

However, since I don't think you will always have 3 values ​​in the second field from your first table, you will need to tweak it a bit.

Inspiration of my answer from:

SQL query to split column data into rows and Equivalent to explode () function for working with strings in MySQL

+2


source to share


SELECT f.name, s.course_name FROM table1 AS f INNER JOIN table2 as s ON f.course_id IN (s.course_id)

0


source to share


Using the query below to solve

Select * from table_2 t2 INNER JOIN table_1 t1 on t1.Course Id = t2.course id

      

-4


source to share







All Articles
Loading...
X
Show
Funny
Dev
Pics