Joining to a table with a comma delimited field

Question

Post reply

Joining to a table with a comma delimited field

renrob

SSC-Addicted

Points: 411
More actions
September 24, 2007 at 11:43 am

#177944

Here's an ugly hack I came up with to handle an unusual situation. We have a simple user account table:
CREATE TABLE [dbo].[user_accounts](
[user_guid] [uniqueidentifier]
ROWGUIDCOL NOT NULL CONSTRAINT [DF_user_accounts_user_guid] DEFAULT (newid()),
[emp_id] [varchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[first] [varchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[middle] [varchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last] [varchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [PK_user_accounts] PRIMARY KEY CLUSTERED
Sample data:
'c6ebcde2-94b9-437d-a16c-001cb37734e0', 'AB1234', 'Bob', 'D', 'Builder'
And an associated divisions table:

CREATE
TABLE [dbo].[divisions](
[id] [int]
IDENTITY(1,1) NOT NULL,
[division_key] [varchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[approver] [varchar]
(250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[title] [varchar]
(250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [PK_divisions] PRIMARY KEY CLUSTERED
(
[id]
ASC
)
WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
ON [PRIMARY]
Sample data:
1, 'Dept 1', 'AB1234, CD5678, EF9012', 'Department of Uno'
The 'approver' column in the divisions table contains a comma delimited list of 'emp_id's that have approval rights. We wanted to write something like "user_accounts INNER JOIN divisions ON emp_id IN approver", but obviously it didn't work. However this query did:
SELECT DISTINCT
       dbo
.user_accounts.first
     , dbo.user_accounts.middle
     , dbo.user_accounts.last
     , dbo.user_accounts.emp_id
     , dbo.divisions.approver
     , dbo.divisions.division_key
FROM dbo.divisions
     , dbo.user_accounts
WHERE PATINDEX ('%' + dbo.user_accounts.emp_id + '%', dbo.divisions.approver) > 0
ORDER
BY dbo.divisions.division_key

I don't think that this is the optimal solution, but it works. Hope this helps anyone in the same situation. If there's a better solution, I would be most happy to see it. 😀

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

look in the script contributions for the split() function.

it takes a delimited list and turns it into a table, so your statement woudl simply change to this:

"user_accounts INNER JOIN divisions ON emp_id IN (select * from dbo.split(approver,',') ) --splits list on comma"

some of the functions have ElementID and Element as a two column table in case you need the order of the list, so you'd do id IN (select Element from dbo.split(approver,',') )

it just depends on which contribution you grab.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!