Clever Geek Handbook

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


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


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


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


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






More articles:


All Articles