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
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
source to share