I have the following SQL ode:

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.