Joining a table based on comma separated values

Question

How can I join two tables, where one of the tables has multiple comma separated values in one column that reference an id in another column?

1st table

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

2nd table

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

Answer 1

Maybe this uglyness, I have not checked results:

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

Answer 2

First of all your Database structure is not normalized and should have been. Since it is already set up this way , here's how to solve the issue.

You'll need a function to split your 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'll need to create a view in order to make up for 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 item in your list.

Now that you have a view which generates your separated fields, you can make a normal join on your view, just use your view like you would use a table.

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

However since I don't think you'll always have 3 values in your second field from your first table you'll need to tweak it a little more.

Inspiration of my answer from:

SQL query to split column data into rows and Equivalent of explode() to work with strings in MySQL