Join Two Tables In SQL One Having Comma Separated Values

Here in this post, we will discuss how to join two table in SQL where one of the columns contains multiple values in a comma-separated format and fetch record or data select from two tables (SQL Tables) for reporting purpose.

Sometimes it is required to store identity column values of a table in another table in comma separated format like below image and wanted to fetch records or data from those tables by applying SQL join for reporting purpose.

Later you wanted to fetch records or data from those tables by applying SQL join as a single result.

Join Two Tables In SQL One Having Comma Separated Values

It is little bit tricky to combine two tables in SQL to fetch its record where one table contains record in a comma-separated format.

To join 2 table in SQL where one of the columns contains multiple values in a comma-separated format, you'll need to break down that column into individual values and then join them appropriately. This is a common scenario in relational databases where a column stores multiple values (e.g., tags, categories, or comma-separated list of IDs).

Let's go through a generic SQL example to demonstrate this process.

Getting Started

Let’s take two SQL Tables tblStudent and tblCOURSE as an example which has structure like given in the example. The tblStudent table in the COURSE column, storing course id of tblCourse table in the comma separated value.

SQL Query To Create Table.

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

SQL Query To Insert Data.

 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')  

SQL Query To Join Two Tables.

The folowing is the SQL query to join 2 tables and fetch record by select from different tables in SQL.

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

This method can be adapted to multiple tables join in sql depending on your database system

Thanks