AdventureWorks - Purchasing.vVendor

AdventureWorks

View Name:	Purchasing.vVendor
Description:	Vendor (company) names and addresses and the names of vendor employees to contact.

Field	Type	Nulls?
VendorID	int	No
Name	nvarchar (50)	No
ContactType	nvarchar (50)	No
Title	nvarchar (8)	Yes
FirstName	nvarchar (50)	No
MiddleName	nvarchar (50)	Yes
LastName	nvarchar (50)	No
Suffix	nvarchar (10)	Yes
Phone	nvarchar (25)	Yes
EmailAddress	nvarchar (50)	Yes
EmailPromotion	int	No
AddressLine1	nvarchar (60)	No
AddressLine2	nvarchar (60)	Yes
City	nvarchar (30)	No
StateProvinceName	nvarchar (50)	No
PostalCode	nvarchar (15)	No
CountryRegionName	nvarchar (50)	No

CREATE VIEW [Purchasing].[vVendor] AS
SELECT
v.[VendorID]
,v.[Name]
,ct.[Name] AS [ContactType]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,c.[Phone]
,c.[EmailAddress]
,c.[EmailPromotion]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,[StateProvinceName] = sp.[Name]
,a.[PostalCode]
,[CountryRegionName] = cr.[Name]
FROM [Purchasing].[Vendor] v
INNER JOIN [Purchasing].[VendorContact] vc
ON vc.[VendorID] = v.[VendorID]
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = vc.[ContactID]
INNER JOIN [Person].[ContactType] ct
ON vc.[ContactTypeID] = ct.[ContactTypeID]
INNER JOIN [Purchasing].[VendorAddress] va
ON va.[VendorID] = v.[VendorID]
INNER JOIN [Person].[Address] a
ON a.[AddressID] = va.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode];