Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Joining to a table with a comma delimited field Expand / Collapse
Author
Message
Posted Monday, September 24, 2007 11:43 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 2:06 PM
Points: 133, Visits: 28
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. :D




Post #402177
Posted Monday, September 24, 2007 12:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 12,903, Visits: 32,142
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



--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #402195
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse