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];
GO
EXEC sp_addextendedproperty N'MS_Description', N'Stores (including store addresses) that sell Adventure Works Cycles products to consumers.', 'SCHEMA', N'Sales', 'VIEW', N'vStoreWithAddresses', NULL, NULL
GO