adding a where clause to the view

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

Transact-SQL (2012)

adding a where clause to the view

Author

Topic

ppatel112
Starting Member

35 Posts

Posted - 2014-09-18 : 21:17:04

Hi Peeps,

i have created below view that pull out phone numbers with different types from phone and phonelink tables for persons.

select p.pers_firstname,p.pers_lastname, phm.phon_Number as Mobile, phh.phon_Number as Home,phb.phon_Number as Business
from Person p
LEFT JOIN PhoneLink plm
on p.Pers_PersonId = plm.PLink_RecordId AND plm.Plink_Type = 'Mobile' AND plm.PLink_EntityId = '13' AND plm.Plink_deleted IS NULL
LEFT JOIN Phone phm ON plm.PLink_PhoneId = phm.Phon_PhoneId AND phm.phon_deleted IS NULL
LEFT JOIN PhoneLink plh
on p.Pers_PersonId = plh.PLink_RecordId AND plh.Plink_Type = 'Home' AND plh.PLink_EntityId = '13' AND plh.Plink_deleted IS NULL
LEFT JOIN Phone phh ON plh.PLink_PhoneId = phh.Phon_PhoneId AND phh.phon_deleted IS NULL
LEFT JOIN PhoneLink plb
on p.Pers_PersonId = plb.PLink_RecordId AND plb.Plink_Type = 'Business' AND plb.PLink_EntityId = '13' AND plb.Plink_deleted IS NULL
LEFT JOIN Phone phb ON plb.PLink_PhoneId = phb.Phon_PhoneId AND phb.phon_deleted IS NULL

what i want to do is to add a where clause to not return null rows in the results so i dont want any rows as below.

firstname lastname home mobile business
test test NULL NULL NULL

also, there there any way to make this view dynamic so that if any new phone types are added it will cater that requirement instead of adding new joins?

please advise.

regards,
parth

ppatel112
Starting Member

35 Posts

Posted - 2014-09-18 : 21:34:08

i just placed a where clause at the end and it did the trick

WHERE phm.phon_Number IS NOT NULL OR phh.phon_Number IS NOT NULL OR phb.phon_Number IS NOT NULL

also, there there any way to make this view dynamic so that if any new phone types are added it will cater that requirement instead of adding new joins?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources