trying to return a value with a single select

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 2000 Forums

Transact-SQL (2000)

trying to return a value with a single select

Author

Topic

LLatinsky
Starting Member

38 Posts

Posted - 2003-06-09 : 11:46:53

-- this is a simplified version of a query I am trying to write.
-- for any given value of "a" I need to get a value of "b" that
--corresponds to 'true',
-- in case there is no 'true' than 'false'. Only when there's
--no 'true' or 'false'
-- get the value of 'unknown'.
-- right now I can achieve this only with having separate queries to
--test the condition
-- any help will be greatly appreciated
create table table1(a int, b int)
go
insert table1
select 1,123
union all
select 1, 234
union all
select 1 , 567
union all
select 1, 678
union all
select 2,123
union all
select 2, 678
union all
select 3, 456
go
create table table2(b int, c varchar(10))
go
insert table2
select 123,'true'
union all
select 234,'false'
union all
select 567,'unknown'
go
-- this is my shot at it but obviously it doesn't work correctly
declare @b int
select @b =isnull(x.b,isnull(y.b,z.b))
from table1 t left join (select b from table2
where c='true')x on t.b=x.b left join (select b from table2
where c='false')y on t.b=y.b left join (select b from table2
where c='unknown')z on t.b=z.b where a=1 and
t.b in (select b from table2 where c in ('true','false','unknown'))

select @b
go
drop table table1
go
drop table table2
go

-- thank you

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-09 : 12:19:29

Something like

declare @b int
select top 1 @b = t.b
from table1 t
join table2 t2
on t2.b = t.b
where t.a=1
order by case when t2.c = 'true' then 1
when t2.c = 'false' then 2
when t2.c = 'unknown' then 3
end
select @b

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

LLatinsky
Starting Member

38 Posts

Posted - 2003-06-09 : 12:44:56

Thank you, nr. Just what I needed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources