Splitting a list to use in a join

[dhulbert]

I have 2 tables 1 contains a field for job priority, the other contans a list of valid priorities.

All I need to do is use the list in table 2 as part of the select from table1

Table1 priority contains

a
b
c
c
d
e

Table2 prioritylist contains

a,b,d


select *
from table1
where priority in (priority list from table2)

Ideas please,

Thanks.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[Olaf Doschke]

select table1.*
from table1
left join table2 on ...your join condition here...
WHERE CHARINDEX(table1.priority, table2.prioritylist)>0

Bye, Olaf.

 

[dhulbert]

Doh.

Obvious and I missed it, Thanks Olaf.

Maybe I should go home and start today again......

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[Olaf Doschke]

No, that's not really a homer moment.

Your initial idea to split the list into a table to join is also very useful and though it's simpler to use CharIndex() you have to have that idea.

I use a split method I found at server central, which eanbles you to do an inner join with the list elements turned into records via a table valued function. Look out for Tally Table, you also might get a new version of it and additional thoughts:

Create Function [dbo].[Splitter] (@Parameter VARCHAR(MAX))
RETURNS @splitResult table (Number int, [Value] varchar(100))
as
begin
SET @Parameter = ','+@Parameter +',';

 WITH cteTally AS
   (
      SELECT TOP (LEN(@Parameter))
         ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
         FROM Master.sys.All_Columns t1
         CROSS JOIN Master.sys.All_Columns t2
   )
 insert @splitResult
   SELECT ROW_NUMBER() OVER (ORDER BY N) AS Number,
    SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1) AS [Value]
    FROM cteTally
       WHERE N < LEN(@Parameter) AND SUBSTRING(@Parameter,N,1) = ','
 return
end 
GO

You can then also Inner join [dbo].[Splitter]("a,b,d") as [TallyTable] On [TallyTable].[Value] = Table1.priority

In this case it's of course easier to use Charindex(), perhaps even not a table2, but simply CharIndex(table1.prioerity,@prioritylist), but in general Splitter() can be a very handy table valued function.

Bye, Olaf.