View: [AdventureWorks2016CTP3].[Purchasing].[vVendorWithContacts]

CollapseAll image

Collapse image View properties


Name  Value 
 Schema   [Purchasing] 
 Owner   [dbo] 
 Creation date   16.11.2015 
 Is schema bound   
 Encrypted   
 ID   1309247719 

Collapse image Creation options


Name Value
QUOTED_IDENTIFIER ON
ANSI_NULLS ON

Collapse image Resultset


Name  Description Data type Max length
BusinessEntityID   int 4
Name   [dbo].[Name] 50
ContactType   [dbo].[Name] 50
Title   nvarchar 8
FirstName   [dbo].[Name] 50
MiddleName   [dbo].[Name] 50
LastName   [dbo].[Name] 50
Suffix   nvarchar 10
PhoneNumber   [dbo].[Phone] 25
PhoneNumberType   [dbo].[Name] 50
EmailAddress   nvarchar 50
EmailPromotion   int 4
Total: 12 column(s)

Collapse image Objects that [Purchasing].[vVendorWithContacts] depends on


Object name Object type Dep level
[Purchasing] Schema 1
[Person].[BusinessEntityContact] Table 1
[Person].[ContactType] Table 1
[Person].[EmailAddress] Table 1
[Person].[Person] Table 1
[Person].[PersonPhone] Table 1
[Person].[PhoneNumberType] Table 1
[Purchasing].[Vendor] Table 1
Total 8 object(s)

Collapse image Extended properties


Name  Value 
MS_Description Vendor (company) names and the names of vendor employees to contact.

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [Purchasing].[vVendorWithContacts] AS 
SELECT 
    v.[BusinessEntityID]
    ,v.[Name]
    ,ct.[Name] AS [ContactType] 
    ,p.[Title] 
    ,p.[FirstName] 
    ,p.[MiddleName] 
    ,p.[LastName] 
    ,p.[Suffix] 
    ,pp.[PhoneNumber] 
    ,pnt.[Name] AS [PhoneNumberType]
    ,ea.[EmailAddress] 
    ,p.[EmailPromotion] 
FROM [Purchasing].[Vendor] v
    INNER JOIN [Person].[BusinessEntityContact] bec 
    ON bec.[BusinessEntityID] = v.[BusinessEntityID]
    INNER JOIN [Person].ContactType ct
    ON ct.[ContactTypeID] = bec.[ContactTypeID]
    INNER JOIN [Person].[Person] p
    ON p.[BusinessEntityID] = bec.[PersonID]
    LEFT OUTER JOIN [Person].[EmailAddress] ea
    ON ea.[BusinessEntityID] = p.[BusinessEntityID]
    LEFT OUTER JOIN [Person].[PersonPhone] pp
    ON pp.[BusinessEntityID] = p.[BusinessEntityID]
    LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
    ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID];
GO
EXEC sp_addextendedproperty N'MS_Description', N'Vendor (company) names  and the names of vendor employees to contact.', 'SCHEMA', N'Purchasing', 'VIEW', N'vVendorWithContacts', NULL, NULL
GO

Collapse image See also


List of views