Use Joined Field In IN Cluase? 1

[Moebius01]

I'm thinking there's no easy way around this, but figured it's worth a try.

I have a table PerfPeriods with two fields, EmpID INT and Periods varchar. Periods contains a comma seperated list.

EmpID Periods
1 1,2
2 2,4


Is there any way to join this to another table, then use the Periods field in the IN Clause?

ie.

SELECT *
FROM Performance a
JOIN PerfPeriods b ON a.EmpID = b.EmpID
WHERE a.Period IN (a.Periods)

Doing that as is returns the error converting to INT as SQL Server doesn't see it as a comma seperated list.

 

[AlexCuse]

Have a look at this FAQ

faq183-5207

What you are attempting is going to be somewhat difficult, and VERY slow. You might consider making a view that displays your table as 1 row per EmpID, Period combination (or actually changing your table)

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ESquared]

Truly, break your Periods out into rows:

EmpID Period
1 1
1 2
2 2
2 4

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[AlexCuse]

For more info on why, see this document:

http://www.r937.com/relational.html

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[sparkbyte]

Thanks Alex, very nice article.

Thanks

John Fuhrman
Titan Global Services

http://www.titangs.com

 

[Moebius01]

I ended up breaking into rows for now.