inner join select where x in y

Question

Hello Community,
I have two tables that I need to combine.

table user
username | customerId
Guido | 1, 2, 3

table customer
customerID | customer name | email address
1 | customer1 | ******@customer1.de
2 | customer2 | ******@customer2.de
3 | customer3 | ******@customer1.de

I would now like to have the following output:
Guido | customer1 | ******@customer1.de
Guido | customer2 | ******@customer2.de
Guido | customer3 | ******@customer2.de

I've tried to build an inner join with "select where customer.customerID in user.customeriD" but this does not work.

Many thanks an best regards

Guido

Answer 1

Could you try below?

CREATE TABLE [User] ([username] VARCHAR(5), [customerId] VARCHAR(100));

INSERT INTO [User] ([username], [customerId])
VALUES ('Guido', '1,2,3');

CREATE TABLE Customer ([customerID] INT, [customer name] VARCHAR(9), [emailaddress] VARCHAR(22));

INSERT INTO Customer ([customerID], [customer name], [emailaddress])
VALUES (1, 'customer1', '******@customer1.de'), (2, 'customer2', '******@customer2.de'), (3, 'customer3', '******@customer1.de');

SELECT username, c.[customer name], c.EmailAddress
FROM [user]
CROSS APPLY string_split(customerId, ',') AS u
INNER JOIN Customer AS c ON u.value = c.customerID

---

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

Answer 2

Which SQL Server version are you using? Since version 2016 we do have the STRING_SPLIT (Transact-SQL) function to achieve your requirements.

Olaf

Answer 3

Glad your problem is solved.
If you have any question, please feel free to let me know.

Regards
Echo