Welcome Guest, you are in: Login

devio dbscript ScrewTurn Wiki

RSS RSS

Navigation





Search the wiki
»

PoweredBy

Page History: Purchasing vVendor (view)

Compare Page Revisions



« Older Revision - Back to Page History - Newer Revision »


Page Revision: 2010/01/09 17:54


wikibot

view Purchasing.vVendor
DescriptionVendor (company) names and addresses and the names of vendor employees to contact.

CREATE VIEW [Purchasing].[vVendor] AS 
SELECT 
    v.[VendorID]
    ,v.[Name]
    ,ct.[Name] AS [ContactType]
    ,c.[Title]
    ,c.[FirstName]
    ,c.[MiddleName]
    ,c.[LastName]
    ,c.[Suffix]
    ,c.[Phone]
    ,c.[EmailAddress]
    ,c.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,[StateProvinceName] = sp.[Name]
    ,a.[PostalCode]
    ,[CountryRegionName] = cr.[Name]
FROM [Purchasing].[Vendor] v
    INNER JOIN [Purchasing].[VendorContact] vc 
    ON vc.[VendorID] = v.[VendorID]
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = vc.[ContactID]
    INNER JOIN [Person].[ContactType] ct 
    ON vc.[ContactTypeID] = ct.[ContactTypeID]
    INNER JOIN [Purchasing].[VendorAddress] va 
    ON va.[VendorID] = v.[VendorID]
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = va.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode];

Dependency TypeObject TypeReferenced Object
SelectTablePerson.Address
SelectTablePerson.Contact
SelectTablePerson.ContactType
SelectTablePerson.CountryRegion
SelectTablePerson.StateProvince
SelectTablePurchasing.Vendor
SelectTablePurchasing.VendorAddress
SelectTablePurchasing.VendorContact

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam.