Joining 2 tables with many-to-many id's, but returning 1 row

April 12, 2023

Table A joins to TABLE B on an ID. Table A column sometimes has a csv of ID’s. I’m only interested in the first ID for the join. The 2nd problem is that table B sometimes has the same ID multiple times. Again, I’m only interested in the first instance of the ID. The other rows can be ignored. So ultimate my result should be 1 row per ID. Thanks to StackOverflow, here’s what I got for the table A CSV solution. The problem I’m left with now is returning 1 row from table b

SELECT a.ID
FROM table a
INNER JOIN table b ON b.id = a.id OR a.id LIKE b.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. Thank you.

>Solution :

You can do it using group by and max() :

SELECT b.ID, max(name) as name
FROM TableA a
 INNER JOIN TableB b
  ON a.id = cast(b.id AS VARCHAR(10)) OR a.id like cast(b.id AS VARCHAR(10))  + ',%'
group by b.ID

I suppose the id in Table B integer this is way I converted it to VARCHAR to match with a.id

Demo here