SQL to join nvarchar(max) column with int column

Question

I need some expert help to do left join on nvarchar(max) column with an int column. I have a Company table with EmpID as nvarchar(max) and this column holds multiple employee ID's separated with commas:

1221,2331,3441

I wanted to join this column with Employee table where EmpID is int.

I did something like below, But this doesn't work when I have 3 empId's or just 1 empID.

SELECT
    A.*, B.empName AS empName1, D.empName AS empName2
FROM 
    [dbo].[Company] AS A
LEFT JOIN
    [dbo].[Employee] AS B ON LEFT(A.empID, 4) = B.empID 
LEFT JOIN
    [dbo].[Employee] AS D ON RIGHT(A.empID, 4) = D.empID

My requirement is to get all empNames if there are multiple empID's in separate columns. Would highly appreciate any valuable input.

So you want the join to succeed if there is *any* match among the comma-separated values? — JosephStyons, May 03 '17 at 14:14
You should ***never ever*** put multiple values into a single column!! This is horribly bad design and violates even the **first normal form** of database design. ***DON'T DO IT!*** You'll have nothing but grief and messy code and heartache from it - if you need a relationship between `Company` and `Employee`, you should have a **separate** table that handles and models this - ***that's the proper relational way*** to do it — marc_s, May 03 '17 at 14:20
yes exactly. I wanted to see if there is any match among multiple values. Unfortunately, I cant change the design as this is already running PROD DB. — user7617078, May 03 '17 at 14:41

score 1 · Answer 1 · edited May 23 '17 at 11:54

You should, if possible, normalize your database.
Read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutly yes!.

If, however, you can't change the database structure, you can use LIKE:

SELECT A.*, B.empName AS empName1, D.empName AS empName2
FROM [dbo].[Company] AS A
LEFT JOIN [dbo].[Employee] AS B ON ',' + A.empID + ',' LIKE '%,'+ B.empID + ',%'

JosephStyons · Answer 2 · 2017-05-03T15:00:44.000

It sounds like you need a table to link employees to companies in a formal way. If you had that, this would be trivial. As it is, this is cumbersome and super slow. The below script creates that linkage for you. If you truly want to keep your current structure (bad idea), then the part you want is under the "insert into..." block.

--clean up the results of any prior runs of this test script
if object_id('STACKOVERFLOWTEST_CompanyEmployeeLink') is not null
  drop table STACKOVERFLOWTEST_CompanyEmployeeLink;
if object_id('STACKOVERFLOWTEST_Employee') is not null
  drop table STACKOVERFLOWTEST_Employee;
if object_id('STACKOVERFLOWTEST_Company') is not null
  drop table STACKOVERFLOWTEST_Company;
go

--create two example tables
create table STACKOVERFLOWTEST_Company
(
  ID int
 ,Name nvarchar(max)
 ,EmployeeIDs nvarchar(max)
 ,primary key(id)
)
create table STACKOVERFLOWTEST_Employee
(
  ID int
 ,FirstName nvarchar(max)
 ,primary key(id)
)

--drop in some test data
insert into STACKOVERFLOWTEST_Company values(1,'ABC Corp','1,2,3,4,50')
insert into STACKOVERFLOWTEST_Company values(2,'XYZ Corp','4,5,6,7,8')--note that annie(#4) works for both places
insert into STACKOVERFLOWTEST_Employee values(1,'Bob')  --bob works for abc corp
insert into STACKOVERFLOWTEST_Employee values(2,'Sue')  --sue works for abc corp
insert into STACKOVERFLOWTEST_Employee values(3,'Bill') --bill works for abc corp
insert into STACKOVERFLOWTEST_Employee values(4,'Annie') --annie works for abc corp
insert into STACKOVERFLOWTEST_Employee values(5,'Matthew')  --Matthew works for xyz corp
insert into STACKOVERFLOWTEST_Employee values(6,'Mark')     --Mark works for xyz corp
insert into STACKOVERFLOWTEST_Employee values(7,'Luke')     --Luke works for xyz corp
insert into STACKOVERFLOWTEST_Employee values(8,'John')     --John works for xyz corp
insert into STACKOVERFLOWTEST_Employee values(50,'Pat')     --Pat works for XYZ corp

--create a new table which is going to serve as a link between employees and their employer(s)
create table STACKOVERFLOWTEST_CompanyEmployeeLink
(
  CompanyID int foreign key references STACKOVERFLOWTEST_Company(ID)
 ,EmployeeID INT foreign key references STACKOVERFLOWTEST_Employee(ID)
)

--this join looks for a match in the csv column.
--it is horrible and slow and unreliable and yucky, but it answers your original question.
--drop these messy matches into a clean temp table
--this is now a formal link between employees and their employer(s)
insert into STACKOVERFLOWTEST_CompanyEmployeeLink
select c.id,e.id
from
  STACKOVERFLOWTEST_Company c
  --find a match based on an employee id followed by a comma or preceded by a comma
  --the comma is necessary so we don't accidentally match employee "5" on "50" or similar
  inner join STACKOVERFLOWTEST_Employee e on
       0 < charindex(      convert(nvarchar(max),e.id) + ',',c.employeeids)
    or 0 < charindex(',' + convert(nvarchar(max),e.id)      ,c.employeeids)
order by
  c.id, e.id

--show final results using the official linking table
select
  co.Name as Employer
 ,emp.FirstName as Employee
from
  STACKOVERFLOWTEST_Company co
  inner join STACKOVERFLOWTEST_CompanyEmployeeLink link on link.CompanyID = co.id
  inner join STACKOVERFLOWTEST_Employee emp on emp.id = link.EmployeeID

score 0 · Accepted Answer · answered May 03 '17 at 14:39

You can give STRING_SPLIT a shot.

SQL Server (starting with 2016)

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

CREATE TABLE #Test
(
RowID INT IDENTITY(1,1),
EID INT,
Names VARCHAR(50)
)

INSERT INTO #Test VALUES (1,'John')
INSERT INTO #Test VALUES (2,'James')
INSERT INTO #Test VALUES (3,'Justin')
INSERT INTO #Test VALUES (4,'Jose')
GO

CREATE TABLE #Test1
(
RowID INT IDENTITY(1,1),
ID VARCHAR(MAX)
)

INSERT INTO #Test1 VALUES ('1,2,3,4')
GO

SELECT Value,T.* FROM #Test1
CROSS APPLY STRING_SPLIT ( ID , ',' )
INNER JOIN #Test T ON value = EID

Nice. This does require sql 2016 though. https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql — JosephStyons, May 03 '17 at 15:05

SQL to join nvarchar(max) column with int column

3 Answers3