Sales.vStoreWithAddresses View

Description

Stores (including store addresses) that sell Adventure Works Cycles products to consumers.

Properties

Creation Date	27/10/2017 14:33
Is Schema Bound
Encrypted
Ansi Nulls

Columns

Column Name	Datatype	Length
BusinessEntityID	Integer	4
Name	dbo.Name	50
AddressType	dbo.Name	50
AddressLine1	VarWChar	60
AddressLine2	VarWChar	60
City	VarWChar	30
StateProvinceName	dbo.Name	50
PostalCode	VarWChar	15
CountryRegionName	dbo.Name	50

Objects that Sales.vStoreWithAddresses depends on

Database Object	Object Type	Description	Dep Level
Person.Address	Table	Street address information for customers, employees, and vendors.	1
Person.AddressType	Table	Types of addresses stored in the Address table.	1
Person.BusinessEntity	Table	Source of the ID that connects vendors, customers, and employees with address and contact information.	2
Person.BusinessEntityAddress	Table	Cross-reference table mapping customers, vendors, and employees to their addresses.	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.	3
dbo.Name	User Defined Data Type		2
dbo.NameStyle	User Defined Data Type		5
Person.Person	Table	Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.	4
Sales.SalesPerson	Table	Sales representative current information.	2
Person.StateProvince	Table	State and province lookup table.	1
Sales.Store	Table	Customers (resellers) of Adventure Works products.	1

View Definition

CREATE VIEW [Sales].[vStoreWithAddresses] AS 

SELECT 
    s.[BusinessEntityID] 
    ,s.[Name] 
    ,at.[Name] AS [AddressType]
    ,a.[AddressLine1] 
    ,a.[AddressLine2] 
    ,a.[City] 
    ,sp.[Name] AS [StateProvinceName] 
    ,a.[PostalCode] 
    ,cr.[Name] AS [CountryRegionName] 
FROM [Sales].[Store] s
    INNER JOIN [Person].[BusinessEntityAddress] bea 
    ON bea.[BusinessEntityID] = s.[BusinessEntityID] 
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = bea.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    INNER JOIN [Person].[AddressType] at 
    ON at.[AddressTypeID] = bea.[AddressTypeID];

Related Objects

Sales Schema
AdventureWorks Database