collating records from different SQL Server tables

Question

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?

Vijaya Kadiyala · Answer

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

Bob Lewiston · Answer

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.

Vijaya Kadiyala · Answer

Hi Bob can you list out the table-structures please. Thanks -- Vijaya Kadiyala http://dotnetvj.blogspot.com