HumanResources.vEmployee View

Description

Employee names and addresses.

Properties

Creation Date	08/01/2010 08:41
Is Schema Bound
Encrypted
Ansi Nulls

Columns

Column Name	Datatype	Length
EmployeeID	int	4
Title	nvarchar	8
FirstName	dbo.Name	50
MiddleName	dbo.Name	50
LastName	dbo.Name	50
Suffix	nvarchar	10
JobTitle	nvarchar	50
Phone	dbo.Phone	25
EmailAddress	nvarchar	50
EmailPromotion	int	4
AddressLine1	nvarchar	60
AddressLine2	nvarchar	60
City	nvarchar	30
StateProvinceName	dbo.Name	50
PostalCode	nvarchar	15
CountryRegionName	dbo.Name	50
AdditionalContactInfo	Person.AdditionalContactInfoSchemaCollection	16

Objects that HumanResources.vEmployee depends on

Database Object	Object Type	Description	Dep Level
Person.Address	Table	Street address information for customers, employees, and vendors.	1
Person.Contact	Table	Names of each employee, customer contact, and vendor contact.	1
Person.CountryRegion	Table	Lookup table containing the ISO standard codes for countries and regions.	1
HumanResources.Employee	Table	Employee information such as salary, department, and title.	1
HumanResources.EmployeeAddress	Table	Cross-reference table mapping employees to their address(es).	1
dbo.Flag	User Defined Data Type		2
dbo.Name	User Defined Data Type		2
dbo.NameStyle	User Defined Data Type		2
dbo.Phone	User Defined Data Type		2
Sales.SalesTerritory	Table	Sales territory lookup table.	2
Person.StateProvince	Table	State and province lookup table.	1

View Definition

CREATE VIEW [HumanResources].[vEmployee] 
AS 

SELECT 
e.[EmployeeID]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,e.[Title] AS [JobTitle] 
,c.[Phone]
,c.[EmailAddress]
,c.[EmailPromotion]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,sp.[Name] AS [StateProvinceName] 
,a.[PostalCode]
,cr.[Name] AS [CountryRegionName] 
,c.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c 
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeAddress] ea 
ON e.[EmployeeID] = ea.[EmployeeID] 
INNER JOIN [Person].[Address] a 
ON ea.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp 
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr 
ON cr.[CountryRegionCode] = sp.[CountryRegionCode];

Related Objects