case statement problem

Hi all

I am having a small problem with the case statement,
I have two table, a status table and users table ( i have scripted them below)

create table users
(id int
, user_name char (10) )

insert into users (id, user_name)
values ( 1, 'bob')
insert into users (id, user_name)
values ( 2, 'sue')
insert into users (id, user_name)
values ( 3, 'richard')
insert into users (id, user_name)
values ( 4, 'john')
insert into users (id, user_name)
values ( 5, 'wendy')

create table status
(name char (10)
, status int, sales_manager int, account_manager int)

insert into status (name, status, sales_manager)
values ('test1', 1, 1 )
insert into status (name, status, sales_manager)
values ('test2', 1, 2 )
insert into status (name, status, account_manager)
values ('test3', 2, 3 )
insert into status (name, status, account_manager)
values ('test4', 2, 4 )
insert into status (name, status)
values ('test5', 2 )

What i need to do when i run the below statement it gives me a list of the names
and the managers, if there is a null value returned i want it to display
'No manager assigned' or something like that

select s.name
, 'manager' = case
when status = 1 then u1.user_name
when status = 2 then u2.user_name
else 'no'
end
from status as s
left join users as u1
on u1.id = s.sales_manager
left join users as u2
on u2.id = s.account_manager

thanks

Like this, use COALESCE or ISNULL

select status,s.name
, 'manager' = case
when status = 1 then coalesce(u1.user_name,'No manager assigned')
when status = 2 then coalesce(u2.user_name,'No manager assigned')
else 'no'
end
from status as s
left join users as u1
on u1.id = s.sales_manager
left join users as u2
on u2.id = s.account_manager

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

You can do below:

select s.name
, coalesce(case
when status = 1 then u1.user_name
when status = 2 then u2.user_name
else 'no'
end, 'No manager assigned') as manager
from status as s
left join users as u1
on u1.id = s.sales_manager
left join users as u2
on u2.id = s.account_manager

Also, please don't use the 'column_alias' = expr syntax. This has been deprecated in SQL Server 2005 and will be removed in a future version of SQL Server. See link below for more details:

http://msdn2.microsoft.com/en-us/ms143729(SQL.90).aspx

|||Thanks guys for the answers, sorted!