sql join on comma separated field

1SELECT * FROM `TABLE1`
2JOIN `TABLE2` ON `TABLE1`.`id` = `TABLE2`.`COMMA_SEPERATED_COLUMN` 
3AND `TABLE2`.`COMMA_SEPERATED_COLUMN` REGEXP "(^|,)SPECIFIC_ID(,|$)"
4
5############## OR #######################
6
7SELECT * FROM table1 
8LEFT JOIN table2 ast ON ast.nodeid = c_NodeId 
9AND ',' + ast.GroupNames + ',' LIKE '%,' + table1.GroupAlphabet + ',%';

1select SM.ROLLNO,
2       SM.NAME, 
3       SM.ADDRESS,
4       (
5       select ','+CM.CourseName
6       from dbo.CourseMaster as CM
7       where ','+SM.Course+',' like '%,'+CM.CourseId+',%'
8       for xml path(''), type
9       ).value('substring(text()[1], 2)', 'varchar(max)') as Course
10from dbo.StudentMaster as SM;
11