I have the following sql code:

SELECT itemID, e.rID, carrier, max(scac) as scac, proNumber, claimID+counter, c.dateOn,  (SELECT top 1 rID from entries where rID=e.rID) as rID, refundAmount, pType, pNum, pDate, sentTo, comments FROM Reimbursement_Invoice_items ri INNER JOIN entries e ON e.entryID=ri.entryID INNER JOIN claims c ON c.rID=e.rID INNER JOIN proLookup p ON p.pro=SUBSTRING(e.proNumber + ',', 1, CHARINDEX(',', e.proNumber + ',') -1)  WHERE riID=" & riID & " AND carrier<>'' AND ai<>'ZZ' GROUP BY itemID, e.rID, carrier, scac, proNumber, claimID+counter, c.dateOn,  ri.entryID, refundAmount, pType, pNum, pDate, sentTo, comments ORDER BY itemID

The point is interest is the following:

INNER JOIN proLookup p ON p.pro=SUBSTRING(e.proNumber + ',', 1, CHARINDEX(',', e.proNumber + ',') -1)

Regarding the right side of the join, unfortunately, the e.proNumber has multiple numbers in them separated by a comma (123,456), so i’m just grabbing the first hit. Another unfortunate we have in play here is there are many pronumbers that are duplicated because different clients may use the same numbers. In order to further lower the scope here, i would like to take the (123) and isolate it, based on the prolookup table, by client. How would i go about this? I’m at a loss here. Any and all help greatly appreciated.