SQL SERVER (ON inner join)

Question

how to join two table one having multiple values separates by commas in one column and with other column having single value.......like

1st table                                2nd table 

name       course                       course name    course id
Tanisha     1,2,3                        Cisco             1
Ellen        3,4,5                        c++               2
honesty     4,2,1                       server            3          
                                        java              4
                                        dot net           5

Answer 1

You can join the tables using like:

select *
from table1 t1 join
     table2 t2
     on ',' + t1.course + ',' like '%,'' + cast(t2.courseid as varchar(255)) + ',%';

In practice, this is a horrid data structure. It stores numerical ids in a string field. It stores lists in a comma delimited field. The engine cannot take advantage of indexes for a basic query, such as the one above. Relational databases have a great data structure for lists; it is called a table. In this case the table would be a junction table.

Sometimes, you have to live with data formats that others have created. If you cannot restructure the data, sometimes SQL is powerful enough to still let you accomplish what you need to do.