query non-existant values - SQL Server Forums

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)

query non-existant values

Author

Topic

eevans
Starting Member

48 Posts

Posted - 2009-02-02 : 11:30:20

I have two tables, name_master and phone_master. Phone_master has a column labeled phn_code which specifies the type of phone number (i.e. home, work, cell) a person has. A person may have rows for all or none of these. If, for example, an employee does not have cell phone, there will be no row containing a NULL value, it simply won't exist period.

Question... How would I write a query to return only cell phone numbers only but list every employee as well. For employees without a cell phone number I would like it to return a blank.

Ex.
id_num phn_cde phn_number
123456 cellphn (555)123-1234
234567 cellphn (555)234-2345
345678
456789
567890 cellphn (555)567-5678

Thanks.
Eric

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-02 : 11:39:16

please post table structure for these two tables

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-02 : 11:46:27

Hi try this,

select nm.id_num, case when pm.phn_cde = 'cellphn' then pm.phn_cde else '' end as phn_cde ,
COALESCE(pm.phn_number,'') AS phn_number
from @name_master nm
LEFT JOIN @phone_master pm on pm.id_num = nm.id_num AND pm.phn_cde='cellphn'

assuming id_num field is common in both tables

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 12:00:15

quote:
Originally posted by eevans

I have two tables, name_master and phone_master. Phone_master has a column labeled phn_code which specifies the type of phone number (i.e. home, work, cell) a person has. A person may have rows for all or none of these. If, for example, an employee does not have cell phone, there will be no row containing a NULL value, it simply won't exist period.

Question... How would I write a query to return only cell phone numbers only but list every employee as well. For employees without a cell phone number I would like it to return a blank.

Ex.
id_num phn_cde phn_number
123456 cellphn (555)123-1234
234567 cellphn (555)234-2345
345678
456789
567890 cellphn (555)567-5678

Thanks.
Eric


SELECT nm.id_num AS EmpNo,
COALESCE(pm.phn_cde,'') AS Code,
COALESCE(pm.phn_number,'') AS PhoneNo
FROM name_master nm
LEFT JOIN phone_master pm
ON pm.id_num=nm.id_num
AND pm.phn_cde='cellphn'

eevans
Starting Member

48 Posts

Posted - 2009-02-02 : 13:28:54

Thanks! That worked.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-03 : 09:11:05

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources