Join two tables one having multiple values in comma separated

Sometimes it is required to keep identity column values of a table in another table with comma separated format like below image.

Join two tables one having multiple values in comma separated

While fetching data from both tables using join, we are facing problems, because identity column values are stored in the comma-separated format.

In this blog we will demonstrate how to short out this problem in SQL. Let’s start without wasting any time.

Getting Started

In the demonstration of this blog we have taken two SQL Tables (tblStudent and tblCOURSE) as an example. The tblStudent table in the COURSE column, storing course id of tblCourse table in the comma separated value like above image.

Below SQL Code create above mentioned table.

 CREATE TABLE tblStudent  
 (  
  ID INT,  
  STUDENTNAME NVARCHAR(100),  
  COURSEID NVARCHAR(100)  
 )  
 CREATE TABLE tblCOURSE  
 (  
  COURSEID INT,  
  COURSENAME NVARCHAR(100)  
 )  

For inserting data into above mentioned table use below code.

 INSERT INTO tblCOURSE VALUES (1,'NIIT')  
 INSERT INTO tblCOURSE VALUES (2,'MCA')  
 INSERT INTO tblCOURSE VALUES (3,'MBA')  

 INSERT INTO tblStudent VALUES (1,'KAILASH','1,2')  
 INSERT INTO tblStudent VALUES (2,'BABU','1,2,3')  
 INSERT INTO tblStudent VALUES (3,'Geeta','1,3')  

Now we will see how to join both table for fetching data. See the below code which joins both table

 SELECT   
                 ST.ID,  
                 ST.STUDENTNAME,  
                 CR.COURSENAME  
 FROM    
                                         [dbo].[tblStudent] AS ST   
                 LEFT JOIN      [dbo].tblCOURSE CR  
                 ON ',' + ST.COURSEID + ',' like '%,' + cast(CR.COURSEID AS NVARCHAR(20)) + ',%'  

In the above code example, the pin point is that in join the COURSEID column value of tblCOURSE gets converts into varchar then it joins with the course column of tblStudent.

Thanks