# SQL Query for max of sum and look up

I have two tables (Table 1 & Table 2) as below

Table 1
ID      Code      V1      V2
--                  ------             ---                  ----
1      E-1      2      1
1      E-12      1      2
1      E-10      3      1
1      E-9      3      2
1      E-8      5      8
1      E-3      8      8
2      E-3      1      3
2      E-5      2      4

Table 2
Code      Desc
-----              -------
E-1      asd
E-2      dsa
E-4      dd
E-6      asd
E-7      asdddd
E-8      asds
E-9      sds
E-10      qwqwe
E-11      da
E-12      cccc

For each ID in Table 1 , i need to get a Desc from table 2 which works as below

In Table 1, for each ID, I need to select a 'Code' which was maximum sum of V1 and V2

for that Code, I need the DESC.

How can i workout the query for this ?
cynx

just to give an example, for ID 1 in table 1, E-3 will be selected, as V1&V2 sum is maximum (16) for ID 1
and the desc will be 'ad'
try

select a.id, a.code, a.v1, a.v2, b.desc
from table1 a
inner join table2 b on a.code = b.code
where a.v1 + a.v2 = (select max(v1 + v2) from yourtable where id = a.id)

guess i missed out one point, the CODE in table 2 is 1,2,3,4,5....
where as in Table 1 it is E-1-2,E-12-23, hence i need to compare it with middle value of E-1-2, so it will be 1 in this case.
Since the lenght of string is not fixed, as it can be E-1-2 or E-12-14, in these cases i need to compare with 1 and 12, how do i get the middle number, as substring function wont help in this case ?

something like PATINDEX but to search from right to left ?

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.

sorry i am not able to execute the query, yourtable will be Table1  ?
I dont get any errors, but no resutls returned

guess the LIke part is not returning anything

wont this be other way round ?
b.code like '%-' + cast(a.code as varchar) + '-%'
as
a.code like '%-' + cast(b.code as varchar) + '-%'
well if the sample data is like this

Table 1
ID      Code      V1      V2
--                  ------             ---                  ----
1      1      2      1
1      12      1      2
1      10      3      1
1      9      3      2
1      8      5      8
1      3      8      8
2      3      1      3
2      5      2      4

Table 2
Code      Desc
-----              -------
E-1-1     asd
E-2-13      dsa
E-4-1      dd
E-6-14      asd
E-7-55      asdddd
E-8-8      asds
E-9-1      sds
E-10-1      qwqwe
E-11-1     da
E-12-2      cccc

Then it should be

select a.id, a.code, a.v1, a.v2, b.desc
from table1 a
inner join table2 b on b.code like '%-' + cast(a.code as varchar) + '-%'
where a.v1 + a.v2 = (select max(v1 + v2) from yourtable where id = a.id)

now I wonder what datatype is code on table1? I've assumed it's int, but if it's varchar it might be an issue with some extra spaces there, so you might want to use ltrim

select a.id, a.code, a.v1, a.v2, b.desc
from table1 a
inner join table2 b on b.code like '%-' + rtrim(ltrim(a.code)) + '-%'
where a.v1 + a.v2 = (select max(v1 + v2) from yourtable where id = a.id)