0

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
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
md zishan Rashid
  • 83
  • 1
  • 1
  • 3
  • 2
    You should redesign this properly. – Martin Smith Oct 03 '14 at 20:47
  • 1
    Use some function to normalize the data in the course field and then join the normal way. And slap the db designer around a bit for not normalizing the tables properly. – jpw Oct 03 '14 at 20:47
  • If you want to solve your problem, look at this question: http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x – jpw Oct 03 '14 at 20:53
  • Also, what would you want as a result of the `JOIN`? – Lamak Oct 03 '14 at 20:53
  • 2
    Comma-separate data in a single column is a huge anti-pattern. The schema design is flawed. Fix it so this no longer happens and suddenly this is question is easy to answer. – Joel Coehoorn Oct 03 '14 at 20:55

1 Answers1

3

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786