CODEWITHSUNDEEP
sql-server
md zishan Rashid
md zishan Rashid

Reputation: 83

Joining 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 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

Upvotes: 8

Views: 31958

Answers (4)

Jeredepp
Jeredepp

Reputation: 222

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

Upvotes: 2

Zahid Gani
Zahid Gani

Reputation: 165

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

Upvotes: -1

Arvo
Arvo

Reputation: 10570

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)) + ',%'

Upvotes: 22

Trushal Makwana
Trushal Makwana

Reputation: 9

Use the Below Query For Solution

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

Upvotes: -4

Related Questions