4

I have two tables that look like the following:

PKey      Description    Group
1AF2      Item 1         101
2B2E4     Item 2         102
3G54A1    Item 3         101
4TR7      Item 4         103

And like this:

PKey                      Rate 1    Rate 2
1AF1/1AF2/1AF3            .075      .0089
2B2E1/2B3E3/2B1E4/2B7E5   .068      .049
3G54A1/3H52A4/3G14B2      .025      .0099
4TR3/4TR7/4TR8            .045      .0012

I need to return the Rate 1 and Rate 2 for each item PKey, but whoever made table 2 didn't know enough about how SQL databases work to actually make the table useful (trying to save time or something).

Is there any easy way to return the info? It will always be divided by slashes from other PKey's in its row, but it can fall at the beginning, arbitrarily at some point in the middle, or the end of the row. In addition, some items might not appear in a row at all.

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
phroureo
  • 377
  • 3
  • 16
  • I've +1ed this largely because you recognize how horrible the structure is and included that fact in your question. Thank you. By the way, the correct way to structure this would be with a bridge table (many-to-many relationship). – jpmc26 Apr 02 '16 at 19:30
  • Yeah, the "Rate" values don't even correspond to any other tables. This table literally just floats there by itself. I'm not sure why they just didn't add it as an attribute on another table. – phroureo Apr 02 '16 at 20:30
  • I meant a bridge table between a "rates" table (probably need a surrogate key) and the main table. My assumption was that if the rates changed, they would need to change for all the related entries; having a separate rates table would allow for that. Obviously, if that's not true, then it should just have been attributes on the original table. – jpmc26 Apr 02 '16 at 22:43

1 Answers1

4

So the PKey in table2 contains a concatenated list of single PKeys. You need a string operation to match records, namely something like '/1AF1/1AF2/1AF3/' like '%/1AF2/%':

select *
from t1
join t2 on '/' + t2.pkey + '/' like '%/' + t1.pkey + '/%';
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Will this take care of the instances where it doesn't start with a `'/'` or doesn't end with a `'/'`? Sometimes the items are at the beginning or the end of the concatenated list. – phroureo Apr 02 '16 at 20:29
  • 2
    This is why I add the slashes here `'/' + t2.pkey + '/'` and here `'%/' + t1.pkey + '/%'`. – Thorsten Kettner Apr 02 '16 at 21:07
  • 1
    This is the right answer. Be aware that if table 2 is huge in terms of row count, this is going to be a slow query. If this is the case your best bet is to refactor and rebuild the table with a normalized structure, it could save you from your users complaining of performance issues. – Overhed Apr 03 '16 at 02:28
  • Agree @Overhed why try to make something already bad work, just take the time to rebuild your table like it should've been done in the first place, it will save you a ton of time in and headaches in the future. – Just Do It Apr 06 '16 at 15:58