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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 extract from col1 if not found then col2

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 #test
where col1 in (select col1 from #test1)

if col1 data is not matched then search in col2

select * from #test
where col2 in (select col1 from #test1)

-- output should be
------------------------------------

abc efg sweet
abc hik sour
xyz efg salty
lmn efg neat

create 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 #test
where col1 in (select col1 from #test1)
union all
select * from #test
where col2 in (select col1 from #test1)
and not exists (select * from #test
where 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.
Go to Top of Page

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,col3
FROM
#test t
WHERE EXISTS
(
SELECT * FROM #test1 t1
WHERE t1.col1 = t.col1 OR t1.col1 = t.col2
)
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -