CREATE VIEW [dbo].[vwCustomer]
AS
SELECT        c.Id, c.Username, c.Email, c.EmailToRevalidate, c.FirstName, c.LastName, c.Gender, c.Company, c.StreetAddress, c.StreetAddress2, c.ZipPostalCode, c.City, c.County, c.Phone, c.Fax, c.VatNumber, c.TimeZoneId, 
                         c.CustomCustomerAttributesXML, c.DateOfBirth, c.SystemName, c.LastIpAddress, c.CurrencyId, c.LanguageId, c.BillingAddress_Id, c.ShippingAddress_Id, c.CustomerGuid, c.CountryId, c.StateProvinceId, c.VatNumberStatusId, 
                         c.TaxDisplayTypeId, c.AdminComment, c.IsTaxExempt, c.AffiliateId, c.VendorId, c.HasShoppingCartItems, c.RequireReLogin, c.FailedLoginAttempts, c.CannotLoginUntilDateUtc, c.Active, c.Deleted, c.IsSystemAccount, 
                         c.CreatedOnUtc, c.LastLoginDateUtc, c.LastActivityDateUtc, c.RegisteredInStoreId, Ship.FirstName AS ShipFirstName, Ship.LastName AS ShipLastName, Ship.Email AS ShipEmail, Ship.Company AS ShipCompany, 
                         Ship.County AS ShipCounty, Ship.City AS ShipCity, Ship.Address1 AS ShipAddress1, Ship.Address2 AS ShipAddress2, Ship.ZipPostalCode AS ShipZipPostalCode, ShipC.Name AS ShipCountry, 
                         Ship.PhoneNumber AS ShipPhoneNumber, Ship.FaxNumber AS ShipFaxNumber, Bill.FirstName AS BillFirstName, Bill.LastName AS BillLastName, Bill.Email AS BillEmail, Bill.Company AS BillCompany, 
                         Bill.County AS BillCounty, Bill.City AS BillCity, Bill.Address1 AS BillAddress1, Bill.Address2 AS BillAddress2, Bill.ZipPostalCode AS BillZipPostalCode, BillC.Name AS BillCountry, Bill.PhoneNumber AS BillPhoneNumber, 
                         Bill.FaxNumber AS BillFaxNumber
FROM            dbo.Customer AS c LEFT OUTER JOIN
                         dbo.CustomerAddresses AS AShip ON c.Id = AShip.Customer_Id AND c.ShippingAddress_Id = AShip.Address_Id LEFT OUTER JOIN
                         dbo.CustomerAddresses AS ABill ON c.Id = ABill.Customer_Id AND c.BillingAddress_Id = ABill.Address_Id LEFT OUTER JOIN
                         dbo.Address AS Ship ON AShip.Address_Id = Ship.Id LEFT OUTER JOIN
                         dbo.Address AS Bill ON ABill.Address_Id = Bill.Id LEFT OUTER JOIN
                         dbo.Country AS ShipC ON Ship.CountryId = ShipC.Id LEFT OUTER JOIN
                         dbo.Country AS BillC ON Bill.CountryId = BillC.Id
WHERE        (c.Deleted = 0) AND (c.IsSystemAccount = 0) AND (c.Username IS NOT NULL)
GO