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
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)) + ',%'
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
SELECT f.name, s.course_name FROM table1 AS f INNER JOIN table2 as s ON f.course_id IN (s.course_id)
Using the query below to solve
Select * from table_2 t2 INNER JOIN table_1 t1 on t1.Course Id = t2.course id