AdventureWorks

Basic Index   Expanded Index

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

FieldTypeNulls?
VendorIDintNo
Namenvarchar (50)No
ContactTypenvarchar (50)No
Titlenvarchar (8)Yes
FirstNamenvarchar (50)No
MiddleNamenvarchar (50)Yes
LastNamenvarchar (50)No
Suffixnvarchar (10)Yes
Phonenvarchar (25)Yes
EmailAddressnvarchar (50)Yes
EmailPromotionintNo
AddressLine1nvarchar (60)No
AddressLine2nvarchar (60)Yes
Citynvarchar (30)No
StateProvinceNamenvarchar (50)No
PostalCodenvarchar (15)No
CountryRegionNamenvarchar (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];