find out phone nos which are not used

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

Transact-SQL (2005)

find out phone nos which are not used

Author

Topic

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-05-28 : 06:14:40

hi,

i have a table that contains three phone columns, i need to find out rows which doesnot have a phoneno in any of these three columns.

create table phone
(
name varchar(100),
phone1 varchar(10),
phone2 varchar(10)
)

insert into phone (name,phone1,phone2)
values('ramesh','1234',null),
('suresh',null,null)

select *
from
phone
where 0 = CASE WHEN phone1 IS NULL THEN
CASE WHEN phone2 IS NULL THEN 0 ELSE 1 END
ELSE 1 END

it works. any other better solution.
Thanks

Iam a slow walker but i never walk back

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-05-28 : 06:23:56

select * from phone where phone1 is null and phone2 is null