collating records from different SQL Server tables

Bob Lewiston

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?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vijaya Kadiyala17y ago
Hi

The Joining key between Person.StateProvince and Person.Address is StateProvinceID.

A Person can have multiple contacts so there will be another table which will hold the relationship between Person.Contact and Person.Address

Do you see any other table which has ContactID and AddressID??

Thanks -- Vijaya Kadiyala

http://dotnetvj.blogspot.com
0
Bob Lewiston17y ago
Vijaya Kadiyala:

>can you list out the table-structures please.

Sure. Thanks for taking a look at this for me.

TABLE Person.Contact
COLUMN NAME                 DATA TYPE

ContactID.......................int
NameStyle......................NameStyle:bit
Title...............................nvarchar(8)
FirstName.......................Name:nvarchar(50)
MiddleName....................Name:nvarchar(50)
LastName.......................Name:nvarchar(50)
Suffix.............................nvarchar(10)
EmailAddress...................nvarchar(50)
EmailPromotion................int
Phone............................Phone:nvarchar(25)
PasswordHash.................varchar(128)
PasswordSalt..................varchar(10)
AdditionalContactInfo.......xml(CONTENT Person.AdditionalContactInfoSchemaCollection)
rowguid..........................uniqueidentifier
ModifiedDate...................datetime

TABLE Person.Address
COLUMN NAME                 DATA TYPE

AddressID.......................int
AddressLine1...................nvarchar(60)
AddressLine2...................nvarchar(60)
City................................nvarchar(30)
StateProvinceID...............int
PostalCode......................nvarchar(15)
rowguid..........................uniqueidentifier
ModifiedDate...................datetime

TABLE Person.StateProvince
COLUMN NAME                 DATA TYPE

StateProvinceID...............int
StateProvinceCode...........nchar(3)
CountryRegionCode..........nvarchar(3)
IsOnlyStateProvinceFlag....Flag:bit
Name.............................Name:nvarchar(50)
TerritoryID......................int
rowguid..........................uniqueidentifier
ModifiedDate...................datetime

I'd also like to tie in table Person.StateProvince, so I can display the state or province's StateProvinceCode (like "NY") or Name (like "New York"), but no doubt that's done via the column StateProvinceID, which is both the Person.Address and the Person.StateProvince tables.
0
Vijaya Kadiyala17y ago
Hi Bob,

can you list out the table-structures please.

Thanks -- Vijaya Kadiyala

http://dotnetvj.blogspot.com
0