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 |
sachingovekar
Posting Yak Master
101 Posts |
Posted - 2012-12-12 : 08:22:03
|
hi,i have a table and want to extract data using a condition.select * from #testwhere col1 in (select col1 from #test1)if col1 data is not matched then search in col2select * from #testwhere col2 in (select col1 from #test1)-- output should be------------------------------------abc efg sweetabc hik sourxyz efg saltylmn efg neatcreate table #test1(col1 varchar(100))insert into #test1 values ('abc')insert into #test1 values ('efg')create table #test(col1 varchar(100),col2 varchar(100),col3 varchar(100))insert into #test values ('abc','efg','sweet') insert into #test values ('abc','hik','sour') insert into #test values ('xyz','efg','salty') insert into #test values ('lmn','efg','neat') insert into #test values ('fju','asw','write') insert into #test values ('fnmjju','assssw','cc') |
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-12-12 : 08:28:16
|
select * from #testwhere col1 in (select col1 from #test1)union allselect * from #testwhere col2 in (select col1 from #test1)and not exists (select * from #testwhere col1 in (select col1 from #test1))==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-12 : 08:29:56
|
Another way would be this - I like this a little better than Nigel's because it is more readable (to me ;)SELECT col1,col2,col3FROM #test tWHERE EXISTS( SELECT * FROM #test1 t1 WHERE t1.col1 = t.col1 OR t1.col1 = t.col2) |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-12-12 : 09:30:13
|
That will get rows if it matches with col1 or col2.Mine gets rows for matches on col1 and if there are none gets rows that match on col2.Not really clear what is wanted.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-12 : 09:34:00
|
quote: Originally posted by nigelrivett That will get rows if it matches with col1 or col2.Mine gets rows for matches on col1 and if there are none gets rows that match on col2.Not really clear what is wanted.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
As usual, you are right, Nigel. On reading the original post again, I think OP is asking for the latter of the two. |
|
|
|
|
|
|
|