Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
rickincanada
Starting Member
18 Posts |
Posted - 2011-03-30 : 10:29:20
|
I have a need to return multiple row values to later be used within a WHERE clause. I'm sure this is possible however I need some help to understand how to do it. Here is a simple example of my tables and SELECT.id link_id security_code1 1 12 1 23 2 14 2 25 2 36 3 17 3 28 3 3I need to SELECT DISTINCT the security_code values in a format that can be used within an IN clause later on in the same statement. An example here would be:SELECT DISTINCT (security_code) FROM table WHERE link_id = '2' AS ''security_filter'I would want to get back something along the lines of (1, 2, 3) so that later on in the WHERE clause of my main SELECT I can write something to the effect of: WHERE security_code IN 'security_filter'. I'm not sure if this can be done all in one statement however I'm hoping so. Thanks for any help you can provide! |
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2011-03-30 : 10:36:33
|
EEK!quote: I would want to get back something along the lines of (1, 2, 3) so that later on in the WHERE clause of my main SELECT I can write something to the effect of: WHERE security_code IN 'security_filter'. I'm not sure if this can be done all in one statement however I'm hoping so. Thanks for any help you can provide!
that would not be valid syntax...you would need to do something more likeWhere securityFilter like +'%,'+convert(varchar,security_code)+',%' i think this will do it... Select link_id, security_filter = ',' + security_code From (Select distinct link_id from table) Z Cross Apply ( Select Cast(security_code As varchar) + ', ' From yourTable A Where Z.link_id = A.link_id Group By A.link_id, A.security_code For Xml Path('') ) Y (security_code) Group By link_id, Projects Corey I Has Returned!! |
 |
|
rickincanada
Starting Member
18 Posts |
Posted - 2011-03-31 : 11:06:52
|
Disregard, I've found my way through this. |
 |
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-03-31 : 11:13:01
|
Why don't you just do a JOIN or EXISTS query? |
 |
|
rickincanada
Starting Member
18 Posts |
Posted - 2011-03-31 : 11:22:29
|
Disregard. I've found my way through this. |
 |
|
rickincanada
Starting Member
18 Posts |
Posted - 2011-03-31 : 11:46:03
|
Thank you all for your help on this! |
 |
|
|
|
|
|
|