Join Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Join Tables

Author

Topic

nikoo56
Starting Member

26 Posts

Posted - 2014-12-23 : 16:56:28

I have a customer table that has primary information about the customer like, name, lastname,password,… I have a Address, Email, Phone table that has for example 3 kinds of address , 2 phone number, 2 email address for each customer. I have a Type table that TypeID and Type_Group,Type_Value. For example:

TypeID Type_Group Type_Value
1 Address Work
2 Address Home
3 Address mailing
4 Email Primary
5 Email secondary
I know how to join customer table with address, Email and phone table. I don't know how to join the Address, phone,Email with type Table.

This is my Query:

SELECT
cc.[Customer_ID]
,[Account_Number]
,[First_Name]
,[Middle_Name]
,[Last_Name]
,[Password]
,ce.[Email]
,cph.Phone_Number
,ca.Address_1
,ca.Address_2
,ca.City
,ca.State
,ca.Zip
,tp.Type_Desc

FROM [CustomerPortal].[dbo].[Customer] cc WITH (NOLOCK)
left join [CustomerPortal].[dbo].Customer_Email ce WITH (NOLOCK) on cc.Customer_ID = ce.Customer_ID
left join [CustomerPortal].[dbo].Customer_Address ca WITH (NOLOCK) on cc.Customer_ID =cp.Customer_ID
left join [CustomerPortal].[dbo].Customer_Phone cph WITH (NOLOCK) on cc.Customer_ID =cph.Customer_ID

WHERE cc.Customer_ID=@Customer_ID

this is customer Table:

(PRIMARY KEY)[Customer_ID] [int] IDENTITY(1,1) NOT NULL,
[Account_Number] [int] NULL,
[First_Name] [varchar](50) NULL,
[Middle_Name] [varchar](50) NULL,

Customer Address Table:

[dbo].[Customer_Address](
primary key[Customer_Address_ID] [int] IDENTITY(1,1) NOT NULL,
Fkey [Customer_ID] [int] NOT NULL,
[Address_1] [varchar](100) NULL,
[Address_2] [varchar](100) NULL,
[City] [varchar](100) NULL,
[State] [varchar](10) NULL,
[Zip] [varchar](10) NULL,
Fkey [Address_Type] [int] NULL,

CustomerEmail Table:

[dbo].[Customer_Email](
PKey [Customer_Email_ID] [int] IDENTITY(1,1) NOT NULL,
Fkey [Customer_ID] [int] NOT NULL,
[Email] [varchar](50) NULL,
Fkey [Email_Type] [int] NULL,

Customer Phone Table:
PK [dbo].[Customer_Phone](
FK [Customer_Phone_ID] [int] IDENTITY(1,1) NOT NULL,
[Customer_ID] [int] NOT NULL,
[Phone_Number] [bigint] NULL,
FK [Phone_Type] [int] NULL,

Type Table:
PK [dbo].[Type_XREF](
[Type_ID] [int] IDENTITY(1,1) NOT NULL,
[Type_Group] [varchar](25) NULL,
[Type_Value] [varchar](50) NULL,
[Type_Desc] [varchar](100) NULL,

I am not sure how to add join to Type Table, any thing I try produce me several Rows, as I want to have 1 row for customer with name, All related Home address,All related mailing address, primary email,secondary email,.... So all customer info in 1 line of record.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-23 : 17:04:37

We'll need to see the table definitions for those tables.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

nikoo56
Starting Member

26 Posts

Posted - 2014-12-23 : 17:16:26

this is customer Table:

(PRIMARY KEY)[Customer_ID] [int] IDENTITY(1,1) NOT NULL,
[Account_Number] [int] NULL,
[First_Name] [varchar](50) NULL,
[Middle_Name] [varchar](50) NULL,

Customer Address Table:

[dbo].[Customer_Address](
primary key[Customer_Address_ID] [int] IDENTITY(1,1) NOT NULL,
Fkey [Customer_ID] [int] NOT NULL,
[Address_1] [varchar](100) NULL,
[Address_2] [varchar](100) NULL,
[City] [varchar](100) NULL,
[State] [varchar](10) NULL,
[Zip] [varchar](10) NULL,
Fkey [Address_Type] [int] NULL,

CustomerEmail Table:

[dbo].[Customer_Email](
PKey [Customer_Email_ID] [int] IDENTITY(1,1) NOT NULL,
Fkey [Customer_ID] [int] NOT NULL,
[Email] [varchar](50) NULL,
Fkey [Email_Type] [int] NULL,

Customer Phone Table:
PK [dbo].[Customer_Phone](
FK [Customer_Phone_ID] [int] IDENTITY(1,1) NOT NULL,
[Customer_ID] [int] NOT NULL,
[Phone_Number] [bigint] NULL,
FK [Phone_Type] [int] NULL,

Type Table:
PK [dbo].[Type_XREF](
[Type_ID] [int] IDENTITY(1,1) NOT NULL,
[Type_Group] [varchar](25) NULL,
[Type_Value] [varchar](50) NULL,
[Type_Desc] [varchar](100) NULL,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-23 : 17:25:54

join [CustomerPortal].[dbo].Type t1 WITH (NOLOCK) on t1.TypeID = ce.Email_Type
join [CustomerPortal].[dbo].Type t2 WITH (NOLOCK) on t2.TypeID = ca.Address_Type
join [CustomerPortal].[dbo].Type t3 WITH (NOLOCK) on t3.TypeID = cph.Phone_Type

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

nikoo56
Starting Member

26 Posts

Posted - 2014-12-23 : 17:36:48

This Gave me so many duplicate records, I would like to have 1 line of records with all addresses and all phone number related to customer.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-23 : 17:39:09

We'll need more info. Please see this article for how to post: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-23 : 17:46:24

Check out PIVOT.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources