Northwind Database (Document! X Sample)
Sales.vStoreWithAddresses View
AdventureWorks Database > Sales Schema : Sales.vStoreWithAddresses View
Description
Stores (including store addresses) that sell Adventure Works Cycles products to consumers.
Properties
Creation Date27/10/2017 14:33
Is Schema Bound
Encrypted
Ansi Nulls
Columns
 Column NameDescriptionDatatypeLengthAllow NullsDefaultFormula
  Integer4   
  dbo.Name50   
  dbo.Name50   
  VarWChar60   
  VarWChar60  
  VarWChar30   
  dbo.Name50   
  VarWChar15   
  dbo.Name50   
Objects that Sales.vStoreWithAddresses depends on
 Database ObjectObject TypeDescriptionDep Level
Person.Address tablePerson.AddressTableStreet address information for customers, employees, and vendors.1
Person.AddressType tablePerson.AddressTypeTableTypes of addresses stored in the Address table. 1
Person.BusinessEntity tablePerson.BusinessEntityTableSource of the ID that connects vendors, customers, and employees with address and contact information.2
Person.BusinessEntityAddress tablePerson.BusinessEntityAddressTableCross-reference table mapping customers, vendors, and employees to their addresses.1
Person.CountryRegion tablePerson.CountryRegionTableLookup table containing the ISO standard codes for countries and regions.1
HumanResources.Employee tableHumanResources.EmployeeTableEmployee information such as salary, department, and title.3
dbo.Name datatypedbo.NameUser Defined Data Type 2
dbo.NameStyle datatypedbo.NameStyleUser Defined Data Type 5
Person.Person tablePerson.PersonTableHuman beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.4
Sales.SalesPerson tableSales.SalesPersonTableSales representative current information.2
Person.StateProvince tablePerson.StateProvinceTableState and province lookup table.1
Sales.Store tableSales.StoreTableCustomers (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];
See Also

Related Objects

Sales Schema
AdventureWorks Database