collating records from different SQL Server tables

[thedougster]

I'm starting to learn SQL Server. As part of that effort, I'm experimenting with accessing AdventureWorks sample databases.

In database AdventureWorks, I've been accessing table Person.Contact. Although in that table there are columns for a phone number and an email address, there are no columns for a postal address. I see columns for a postal address in table Person.Address, but in that table there is no column for a numerical value ContactID, or for a string value for the contact's name, as there is in table Person.Contact.

It seems to me, especially because each of these two tables' names start with "Person.", that these tables are somehow linked. However, I don't see any columns in either table which seem to allude to any other tables. For what it's worth though, each of the two tables has a column called "rowguid", the purpose of which I don't understand.

I'd like my app to display in a WinForm all the information available for each contact, but I don't know how to locate the Person.Address record corresponding to a given Person.Contact record. Can anybody tell me how to do this?

 

[markros]

Looks like you're right and no additional direct link exists beween two tables. May be the database is not designed as a good relational database.

Anyway, I found this view

USE [AdventureWorks]
GO

/****** Object: View [Person].[vAdditionalContactInfo] Script Date: 03/16/2009 06:56:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [Person].[vAdditionalContactInfo]
AS
SELECT
[ContactID]
,[FirstName]
,[MiddleName]
,[LastName]
,[ContactInfo].ref.value(N'declare namespace ci="

http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

declare namespace act="

http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

(act:telephoneNumber)[1]/act:number', 'nvarchar(50)') AS [TelephoneNumber]
,LTRIM(RTRIM([ContactInfo].ref.value(N'declare namespace ci="

http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

declare namespace act="

http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

(act:telephoneNumber/act:SpecialInstructions/text())[1]', 'nvarchar(max)'))) AS [TelephoneSpecialInstructions]
,[ContactInfo].ref.value(N'declare namespace ci="

http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

declare namespace act="

http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

(act:homePostalAddress/act:Street)[1]', 'nvarchar(50)') AS [Street]
,[ContactInfo].ref.value(N'declare namespace ci="

http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

declare namespace act="

http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

(act:homePostalAddress/act:City)[1]', 'nvarchar(50)') AS [City]
,[ContactInfo].ref.value(N'declare namespace ci="

http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

declare namespace act="

http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

(act:homePostalAddress/act:StateProvince)[1]', 'nvarchar(50)') AS [StateProvince]
,[ContactInfo].ref.value(N'declare namespace ci="

http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

declare namespace act="

http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

(act:homePostalAddress/actostalCode)[1]', 'nvarchar(50)') AS [PostalCode]
,[ContactInfo].ref.value(N'declare namespace ci="

http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

declare namespace act="

http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

(act:homePostalAddress/act:CountryRegion)[1]', 'nvarchar(50)') AS [CountryRegion]
,[ContactInfo].ref.value(N'declare namespace ci="

http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

declare namespace act="

http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

(act:homePostalAddress/act:SpecialInstructions/text())[1]', 'nvarchar(max)') AS [HomeAddressSpecialInstructions]
,[ContactInfo].ref.value(N'declare namespace ci="

http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

declare namespace act="

http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

(act:eMail/act:eMailAddress)[1]', 'nvarchar(128)') AS [EMailAddress]
,LTRIM(RTRIM([ContactInfo].ref.value(N'declare namespace ci="

http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

declare namespace act="

http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

(act:eMail/act:SpecialInstructions/text())[1]', 'nvarchar(max)'))) AS [EMailSpecialInstructions]
,[ContactInfo].ref.value(N'declare namespace ci="

http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

declare namespace act="

http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

(act:eMail/act:SpecialInstructions/act:telephoneNumber/act:number)[1]', 'nvarchar(50)') AS [EMailTelephoneNumber]
,[rowguid]
,[ModifiedDate]
FROM [Person].[Contact]
OUTER APPLY [AdditionalContactInfo].nodes(
'declare namespace ci="

http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

/ci:AdditionalContactInfo') AS ContactInfo(ref)
WHERE [AdditionalContactInfo] IS NOT NULL;

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'VIEW',@level1name=N'vAdditionalContactInfo'
GO