I have a table with 4 char error codes and the descriptions for them.
I have a table of sales with multiple error codes in a single string.
I designed a join using like which allows for the highest weighted error code to be joined on which works well except when 2 or more errors have the same weight as each other, which creates multiple joined rows.

IF the weights are equal, I just want 1 result (any will do)

TABLE ErrorCodes

```
Code   | Description       | Weight
'0041' | 'data error'      | 4
'0019' | 'format error'    | 2
'0099' | 'missing creator' | 2

```

TABLE MyData

```
ID   | RespCode     
1234 | '00410019'
1235 | '00990019'

```

Results I get

```
ID   | RespCode   | Description
1234 | '00410019' | 'data error' 
1235 | '00990019' | 'format error'
1235 | '00990019' | 'missing creator'

```

I only want 1 result for 1235 but even with Max or Top 1 in sub-select, I get two. How can I limit to 1 joined row?

```
    SELECT * FROM MyData 
    left JOIN ErrorCodes
    ON     MyData.RespCode 
    LIKE '%' + ErrorCodes.Code + '%' 
    AND ErrorCodes.Weight = ( 
        SELECT MAX (Weight) FROM ErrorCodes 
        WHERE  MyDate.RespCode LIKE '%' + ErrorCodes.Code + '%' 
    ) 

```