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 |
|
allan8964
Posting Yak Master
249 Posts |
 Posted - 2012-11-11 : 22:59:54
|
| Hi there,I have a table1 need some variables, could be 3 or 20, in where clause like this:select * from table1where code in ('abc', 'bbc', 'cbs')I need provide the items of 'abc' from table2, like select code from table2where model = @xyz I can use a temporary table to get all code records from table2 but the problem is that with different @xyz the code number could be 3 or 20. Then how can I decide to embed these var in the IN statement? Thanks in advance. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-11 : 23:17:28
|
| DECLARE @xyz int SET @xyz = YourValue ------------eg: 20select * from table1where code in (select code from table2where model = @xyz)--Chandu |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
allan8964
Posting Yak Master
249 Posts |
Posted - 2012-11-12 : 14:01:54
|
| Thanks guys.I think it's my fault not describing my requirement clear. When I say 3 or 20 I didn't mean the value is 3 or 20. Actaully I meant there are 3 or 20 items inside the IN (), like where code in ('abc', 'bbc', 'cbs' .... ) there could be 3 to 20 of those 'abc', 'bbc' ...If col CODE from table2 returns 3 values then I can use a temporary table to feed the IN() but what if table2 returns 20 values? Thanks. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-11-12 : 19:56:08
|
quote:
Originally posted by allan8964
Thanks guys.I think it's my fault not describing my requirement clear. When I say 3 or 20 I didn't mean the value is 3 or 20. Actaully I meant there are 3 or 20 items inside the IN (), like where code in ('abc', 'bbc', 'cbs' .... ) there could be 3 to 20 of those 'abc', 'bbc' ...If col CODE from table2 returns 3 values then I can use a temporary table to feed the IN() but what if table2 returns 20 values? Thanks.
You can still use the query posted by bandi for that. You don't need a temp table to do that
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
allan8964
Posting Yak Master
249 Posts |
Posted - 2012-11-13 : 11:08:55
|
| Yes, it works that way. Thanks guys! |
|
|
|
|
|
|
|
|
How to embed the var in where clause?