Sales.vStoreWithContacts

CREATE VIEW "Sales.vStoreWithContacts" AS
SELECT
    s."BusinessEntityID"
    ,s."Name"
    ,ct."Name" AS "ContactType"
    ,p."Title"
    ,p."FirstName"
    ,p."MiddleName"
    ,p."LastName"
    ,p."Suffix"
    ,pp."PhoneNumber"
	,pnt."Name" AS "PhoneNumberType"
    ,ea."EmailAddress"
    ,p."EmailPromotion"
FROM "Sales.Store" s
    INNER JOIN "Person.BusinessEntityContact" bec
    ON bec."BusinessEntityID" = s."BusinessEntityID"
	INNER JOIN "Person.ContactType" ct
	ON ct."ContactTypeID" = bec."ContactTypeID"
	INNER JOIN "Person.Person" p
	ON p."BusinessEntityID" = bec."PersonID"
	LEFT OUTER JOIN "Person.EmailAddress" ea
	ON ea."BusinessEntityID" = p."BusinessEntityID"
	LEFT OUTER JOIN "Person.PersonPhone" pp
	ON pp."BusinessEntityID" = p."BusinessEntityID"
	LEFT OUTER JOIN "Person.PhoneNumberType" pnt
	ON pnt."PhoneNumberTypeID" = pp."PhoneNumberTypeID"