0

I have a table (a) that contains imported data, and one of the values in that table needs to be joined to another table (b) based on that value. In table b, sometimes that value is in a comma separated list, and it is stored as a varchar. This is the first time I have dealt with a database column that contains multiple pieces of data. I didn't design it, and I don't believe it can be changed, although, I believe it should be changed.

For example:

Table a: 

column_1 
12345
67890
24680
13579

Table b: 

column_1
12345,24680
24680,67890
13579
13579,24680

So I am trying to join these table together, based on this number and 2 others, but when I run my query, I'm only getting the one that contain 13579, and none of the rest.

Any ideas how to accomplish this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rob M
  • 1,007
  • 2
  • 17
  • 38
  • 1
    Please Follow these two **StackOverflow** posts, which has discussed your problem in detail. 1. http://stackoverflow.com/questions/6861258/how-to-join-two-tables-using-a-comma-separated-list-in-the-join-field 2. http://stackoverflow.com/questions/15524457/update-a-database-table-field-with-comma-separated-list-from-join – Josh Jun 23 '13 at 20:37

1 Answers1

3

Storing lists as a comma delimited data structure is a sign of bad design, particularly when storing ids, which are presumably an integer in their native format.

Sometimes, this is necessary. Here is a method:

select *
from a join
     b
     on ','+b.column_1+',' like '%,'+cast(a.column_1 as varchar(255))+',%'

This will not perform particularly well, because the query will not take advantage of any indexes.

The idea is to put the delimiter (,) at the beginning and end of b.column_1. Every value in the column then has a comma before and after. Then, you can search for the match in a.column_1 with commas appended. The commas ensure that 10 does not match 100.

If possible, you should consider an alternative way to represent the data. If you know there are at most two values, you might consider having two columns in a. In general, though, you would have a "join" table, with a separate row for each pair.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I totally agree with the bad design comment. I've actually been trying to think up a better way to store these values. – Rob M Jun 05 '13 at 20:10