SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 extract from col1 if not found then col2		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sachingovekar
Yak Posting Veteran

97 Posts
Posted - 12/12/2012 :  08:22:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3328 Posts
Posted - 12/12/2012 :  08:28:16  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 12/12/2012 :  08:29:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3328 Posts
Posted - 12/12/2012 :  09:30:13  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 12/12/2012 :  09:34:00  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000