multiple alias for different selection fields

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

Transact-SQL (2008)

multiple alias for different selection fields

Author

Topic

ppatel112
Starting Member

35 Posts

Posted - 2011-09-06 : 22:12:54

Hi Guys,

i have following query where there are three different type of phone and i want those be seperate alias and be presented in my system.

(SELECT Emai_EmailAddress
FROM dbo.Email
WHERE (dbo.Person.Pers_PersonId = Emai_PersonID) AND (dbo.Person.Pers_CompanyId = Emai_CompanyID) AND (Emai_Deleted IS NULL) AND
(Emai_Type = N'Private')) AS Pers_Private_Email
--(SELECT Phon_Number
-- FROM dbo.Phone
-- WHERE (dbo.Person.Pers_PersonId = Phon_PersonID) AND (dbo.Person.Pers_CompanyId = Phon_CompanyID) AND (Phon_Deleted IS NULL) AND
-- (Phon_Type = 'Home')) AS Pers_Home_Phone
--(SELECT Phon_Number
-- FROM dbo.Phone AS Phone_3
-- WHERE (dbo.Person.Pers_PersonId = Phon_PersonID) AND (dbo.Person.Pers_CompanyId = Phon_CompanyID) AND (Phon_Deleted IS NULL) AND
-- (Phon_Type = 'Mobile')) AS Pers_Mobile_Phone,
-- (SELECT Phon_Number
-- FROM dbo.Phone AS Phone_2
-- WHERE (dbo.Person.Pers_PersonId = Phon_PersonID) AND (dbo.Person.Pers_CompanyId = Phon_CompanyID) AND (Phon_Deleted IS NULL) AND
-- (Phon_Type = 'Business')) AS Pers_Work_Phone
FROM dbo.Person LEFT OUTER JOIN
dbo.Company ON dbo.Person.Pers_CompanyId = dbo.Company.Comp_CompanyId LEFT OUTER JOIN
dbo.Address ON dbo.Address.Addr_AddressId = dbo.Person.Pers_PrimaryAddressId
WHERE (dbo.Person.Pers_Deleted IS NULL)

is it possible by any means to merge the query into one for all the three phone types.

please advise.

regards,
parth

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 22:41:33

[code]
...
Emai_EmailAddress AS Pers_Private_Email
ph.Pers_Home_Phone
ph.Pers_Mobile_Phone,
ph.Pers_Work_Phone
FROM dbo.Person LEFT OUTER JOIN
dbo.Company ON dbo.Person.Pers_CompanyId = dbo.Company.Comp_CompanyId LEFT OUTER JOIN
dbo.Address ON dbo.Address.Addr_AddressId = dbo.Person.Pers_PrimaryAddressId
LEFT OUTER JOIN (SELECT Phon_PersonID,Phon_CompanyID,
MAX(CASE WHEN Phon_Type = 'Home' THEN Phon_Number ELSE NULL END) AS Pers_Home_Phone,
MAX(CASE WHEN Phon_Type = 'Mobile' THEN Phon_Number ELSE NULL END) AS Pers_Mobile_Phone,
MAX(CASE WHEN Phon_Type = 'Business' THEN Phon_Number ELSE NULL END) AS Pers_Work_Phone
FROM dbo.Phone
WHERE Phon_Deleted IS NULL
GROUP BY Phon_PersonID,Phon_CompanyID)ph
ON dbo.Person.Pers_PersonId = ph.Phon_PersonID
AND dbo.Person.Pers_CompanyId = ph.Phon_CompanyID
LEFT OUTER JOIN (SELECT Emai_PersonID,Emai_CompanyID,Emai_EmailAddress
FROM dbo.Email
WHERE Emai_Deleted IS NULL AND
Emai_Type = N'Private')em
ON (dbo.Person.Pers_PersonId = em.Emai_PersonID)
AND (dbo.Person.Pers_CompanyId = em.Emai_CompanyID)
WHERE (dbo.Person.Pers_Deleted IS NULL)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources