Search code examples
sqlstringinner-joindbeaver

SQL Join help needed field type doesn't match for duration information

I am trying to join two tables with SQL. I am looking to have the cost from table 2 added to table 1. The cost used to only vary by Service so it was a simple join, but the data has changed and now the cost also varies depending upon location and duration.

I am able to link based on location but not duration. The duration in Table 1 is stored as an integer. In table 2 the duration is stored as a text range in the format "xx-xxxx." I tried isolating the minimum duration and maximum duration in new fields using substring/left and patindex. (I'm using a cache database accessed through dbeaver and wasn't able to use charindex).

I was able to extract the minimum duration as the text before the "-" character but wasn't able to extract the maximum duration, and even if I had it I'm not sure how to join the tables. Please help! Also due to the database being used, I'm only able to write select statements at this time, not create/edit tables. Thank you!

Table 1:

table 1

Table 2:

table 2

Desired Output:

output

Solution

  • That's a poor design. You should not be storing number ranges as strings. Instead, you should have the upper and lower bounds in two different columns.

    The way your data is stored, you need to split the string and convert each part to numbers, that you can the compare with the value in the first table. So:

    select t1.*, t2.cost
from table1 t1
inner join table2 t2 
    on t2.duration between cast(substring(duration, 1, instr(duration, '-') - 1) as int) 
                       and cast(substring(duration, instr(duration, '-') + 1) as int)