Table A joins to TABLE B on an ID. The problem I'm finding is that sometimes in table A, the returned column for ID is multiple ID's Separated by a comma. So what I'm trying to do is just to a join based on the 1st id in the CSV list.
SELECT ID, name
FROM TableA a
INNER JOIN TabelB b ON b.id = a.idAlso, please note that the ID's in both tables aren't primary key's. They are just named like that.
Here's what the content looks like in table A/B
Table A
ID Name
10023,2019 Bob
1243 Mary
29853 William
Table B
Company ID
Kroc 10023
Espres 99378
MarcDonalds 10023
etc...In the supplied example data, only Kroc should come up with Bob. Even though there are 2 results in table B, just ignore and return 1. Thank you.
2条答案
按热度按时间dtcbnfnu1#
I see there are already answers posted using
left/substring, but I will note that you can probably also use alikeif you prefer. Depending on your datatypes, something like the following will probably work (or it could be modified to account for your datatypes):You can see it working in this Fiddle . The idea is that it must either match exactly or match up to the first comma.
flvtvl502#
You will need SUBSTRING and CHARINDEX for that.
Something like that
ON b.id = SUBSTRING(a.id + ',', 1, CHARINDEX(',', a.id + ',') -1). You might also need explicit datatype casts.But your tables do not follow 1NF. Maybe it is time to think, whether this is reasonable or not.