Grabbing left side of comma in returned SQL result for JOIN

Question

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.id

Also, 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.

You're going to need to split your id column by "," - there are plenty of examples of how to do that out there. — Dale K, Apr 11 '23 at 21:50
BTW - you do realise SQL Server 2008 is so far out of support as to be dangerous? You should be seriously looking at upgrading asap. — Dale K, Apr 11 '23 at 22:10

score 2 · Accepted Answer · answered Apr 11 '23 at 22:26

2

I see there are already answers posted using left/substring, but I will note that you can probably also use a like if you prefer. Depending on your datatypes, something like the following will probably work (or it could be modified to account for your datatypes):

SELECT b.ID, name
FROM TableA a
 INNER JOIN TableB b
  ON a.id = b.id OR a.id like b.id + ',%'

You can see it working in this Fiddle. The idea is that it must either match exactly or match up to the first comma.

answered Apr 11 '23 at 22:26

EdmCoff

3,506
1
9
9

Just realized that Table B may have multiple of the same ID's. So in this example, it has 2 rows returned with the ID of 10023. How do I force it to return just 1 row back? – Damien Apr 11 '23 at 22:48
I awarded your comment because you met the original criteria. Love the solution actually. Didn't know you could do something like that. I created a new question for the new problem: https://stackoverflow.com/questions/75990727/joining-2-tables-with-many-to-many-ids-but-returning-1-row – Damien Apr 11 '23 at 23:10

score 1 · Answer 2 · edited Jul 05 '23 at 15:31

1

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.

edited Jul 05 '23 at 15:31

halfer

19,824
17
99
186

answered Apr 11 '23 at 22:08

Roasty Chips

75
4

i tried putting something together with left and charindex but the problem now is if there is NO comma, i get an error. I'll try this example. Thank you. – Damien Apr 11 '23 at 22:18
@DaleK yes, you are right. not all of the ids are with comma. I'll edit my answer, thanks – Roasty Chips Apr 12 '23 at 00:26

Grabbing left side of comma in returned SQL result for JOIN

2 Answers2