-1

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 ultimately my result should be 1 row per ID. Thanks to Stack Overflow, 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.

halfer
  • 19,824
  • 17
  • 99
  • 186
Damien
  • 4,093
  • 9
  • 39
  • 52

1 Answers1

2

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

SelVazi
  • 10,028
  • 2
  • 13
  • 29
  • 1
    Wow. You are amazing. It's unbelievable how much knowledge there is out there and how little of it i know. Thank you! – Damien Apr 11 '23 at 23:35