August 16, 2010 at 11:54 pm
Hi All:
I am having trouble creating a query or view. I have 5 customer related tables
1. Customer Key = CustomerID
2. Address Header
3. AddressDetail
4. Customer Phone
5. Customer Email.
I have 10 Customers in my Customer tables I need to create a query or view that will return all 10 distinct customer records with their associated contact info that is based on filters. All tables can be joined on the CustomerID except the AddrDetail which can bo joined on a junction table(AddrHeader) that has the CustomerID and AddressID.
I need to see (all 10 Distinct customers) and their addresses when the address status = 'A', their associated Phone Number when staus = 'H' and their EmailAddress when that staus = 'A' Below is a Script that will create all the tables and the test data.
In my query thus far I am not seening
Here is my query so far;
SELECT C.CustomerID, C.FName, C.LName, AH.AddressStatus, AD.Address1, AD.Address2, AD.City, AD.State, dbo.CustEmail.Emailaddress, dbo.CustEmail.EmailStatus,
dbo.CustPhone.PhoneNo, dbo.CustPhone.PhoneType
FROM dbo.CustPhone RIGHT OUTER JOIN
dbo.Customer AS C ON dbo.CustPhone.CustomerID = C.CustomerID LEFT OUTER JOIN
dbo.CustEmail ON C.CustomerID = dbo.CustEmail.CustomerID LEFT OUTER JOIN
dbo.AddrDetail AS AD INNER JOIN
dbo.AddrHeader AS AH ON AD.AddressID = AH.AddressID ON C.CustomerID = AH.CustomerID
WHERE (AH.AddressStatus = 'A')
With this query I am not returning all customers
I want my final result to be my 10 customer's.
Thanks in advance!
/*** Script **************/
CREATE DATABASE Customer
GO
USE [Customer]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CustPhone](
[CustomerID] [int] NULL,
[PhoneNo] [nchar](10) NULL,
[PhoneType] [nchar](1) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[CustPhone] ([CustomerID], [PhoneNo], [PhoneType]) VALUES (1, N'7773331111', N'B')
INSERT [dbo].[CustPhone] ([CustomerID], [PhoneNo], [PhoneType]) VALUES (2, N'8889991234', N'H')
INSERT [dbo].[CustPhone] ([CustomerID], [PhoneNo], [PhoneType]) VALUES (3, N'1112223333', N'C')
INSERT [dbo].[CustPhone] ([CustomerID], [PhoneNo], [PhoneType]) VALUES (3, N'1112223434', N'H')
INSERT [dbo].[CustPhone] ([CustomerID], [PhoneNo], [PhoneType]) VALUES (4, N'5554448889', N'B')
INSERT [dbo].[CustPhone] ([CustomerID], [PhoneNo], [PhoneType]) VALUES (4, N'7779998888', N'H')
INSERT [dbo].[CustPhone] ([CustomerID], [PhoneNo], [PhoneType]) VALUES (5, N'3331111212', N'B')
INSERT [dbo].[CustPhone] ([CustomerID], [PhoneNo], [PhoneType]) VALUES (5, N'3335454477', N'H')
INSERT [dbo].[CustPhone] ([CustomerID], [PhoneNo], [PhoneType]) VALUES (7, N'4441215554', N'H')
INSERT [dbo].[CustPhone] ([CustomerID], [PhoneNo], [PhoneType]) VALUES (7, N'4441215151', N'B')
INSERT [dbo].[CustPhone] ([CustomerID], [PhoneNo], [PhoneType]) VALUES (8, N'2221234545', N'B')
INSERT [dbo].[CustPhone] ([CustomerID], [PhoneNo], [PhoneType]) VALUES (8, N'2225453333', N'H')
INSERT [dbo].[CustPhone] ([CustomerID], [PhoneNo], [PhoneType]) VALUES (9, N'8992123333', N'C')
INSERT [dbo].[CustPhone] ([CustomerID], [PhoneNo], [PhoneType]) VALUES (10, N'1001112323', N'H')
INSERT [dbo].[CustPhone] ([CustomerID], [PhoneNo], [PhoneType]) VALUES (10, N'1002123333', N'C')
/****** Object: Table [dbo].[Customer] Script Date: 08/17/2010 01:04:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[FName] [nvarchar](20) NULL,
[LName] [nvarchar](20) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Customer] ON
INSERT [dbo].[Customer] ([CustomerID], [FName], [LName]) VALUES (1, N'John ', N'Jones')
INSERT [dbo].[Customer] ([CustomerID], [FName], [LName]) VALUES (2, N'Paul', N'Smith')
INSERT [dbo].[Customer] ([CustomerID], [FName], [LName]) VALUES (3, N'Terry', N'Edwards')
INSERT [dbo].[Customer] ([CustomerID], [FName], [LName]) VALUES (4, N'Ralph', N'Nader')
INSERT [dbo].[Customer] ([CustomerID], [FName], [LName]) VALUES (5, N'Tommy', N'John')
INSERT [dbo].[Customer] ([CustomerID], [FName], [LName]) VALUES (6, N'Michael', N'Jackson')
INSERT [dbo].[Customer] ([CustomerID], [FName], [LName]) VALUES (7, N'Elvis ', N'Presley')
INSERT [dbo].[Customer] ([CustomerID], [FName], [LName]) VALUES (8, N'Buddy ', N'Holly')
INSERT [dbo].[Customer] ([CustomerID], [FName], [LName]) VALUES (9, N'Johan', N'Bach')
INSERT [dbo].[Customer] ([CustomerID], [FName], [LName]) VALUES (10, N'Miles', N'Davis')
SET IDENTITY_INSERT [dbo].[Customer] OFF
/****** Object: Table [dbo].[CustEmail] Script Date: 08/17/2010 01:04:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CustEmail](
[CustomerID] [int] NULL,
[Emailaddress] [nvarchar](150) NULL,
[EmailStatus] [nvarchar](6) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[CustEmail] ([CustomerID], [Emailaddress], [EmailStatus]) VALUES (1, N'jjones@all.net', N'A')
INSERT [dbo].[CustEmail] ([CustomerID], [Emailaddress], [EmailStatus]) VALUES (1, N'jjones@yhoo.com', N'C')
INSERT [dbo].[CustEmail] ([CustomerID], [Emailaddress], [EmailStatus]) VALUES (1, N'jhjones@goml.net', N'C')
INSERT [dbo].[CustEmail] ([CustomerID], [Emailaddress], [EmailStatus]) VALUES (2, N'paul.smith@hotmail.com', N'C')
INSERT [dbo].[CustEmail] ([CustomerID], [Emailaddress], [EmailStatus]) VALUES (2, N'psmith@goml.net', N'A')
INSERT [dbo].[CustEmail] ([CustomerID], [Emailaddress], [EmailStatus]) VALUES (3, N'tedwards@aol.com', N'C')
INSERT [dbo].[CustEmail] ([CustomerID], [Emailaddress], [EmailStatus]) VALUES (4, N'rnader@all.net', N'A')
INSERT [dbo].[CustEmail] ([CustomerID], [Emailaddress], [EmailStatus]) VALUES (5, N'tjohn@yhoo.com', N'C')
INSERT [dbo].[CustEmail] ([CustomerID], [Emailaddress], [EmailStatus]) VALUES (6, N'mjack@yhoo.com', N'C')
INSERT [dbo].[CustEmail] ([CustomerID], [Emailaddress], [EmailStatus]) VALUES (6, N'mjack@hotmail.com', N'A')
INSERT [dbo].[CustEmail] ([CustomerID], [Emailaddress], [EmailStatus]) VALUES (6, N'mjack@aol.com', N'C')
INSERT [dbo].[CustEmail] ([CustomerID], [Emailaddress], [EmailStatus]) VALUES (7, N'elvisp@yhoo.com', N'A')
INSERT [dbo].[CustEmail] ([CustomerID], [Emailaddress], [EmailStatus]) VALUES (8, N'bholly@all.net', N'A')
INSERT [dbo].[CustEmail] ([CustomerID], [Emailaddress], [EmailStatus]) VALUES (8, N'bholly@goml.net', N'C')
INSERT [dbo].[CustEmail] ([CustomerID], [Emailaddress], [EmailStatus]) VALUES (10, N'miles@cool.net', N'A')
INSERT [dbo].[CustEmail] ([CustomerID], [Emailaddress], [EmailStatus]) VALUES (10, N'mdavis@yhoo.com', N'C')
/****** Object: Table [dbo].[AddrHeader] Script Date: 08/17/2010 01:04:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AddrHeader](
[CustomerID] [int] NOT NULL,
[AddressID] [int] NOT NULL,
[AddressStatus] [nchar](1) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[AddrHeader] ([CustomerID], [AddressID], [AddressStatus]) VALUES (10, 1, N'A')
INSERT [dbo].[AddrHeader] ([CustomerID], [AddressID], [AddressStatus]) VALUES (10, 2, N'C')
INSERT [dbo].[AddrHeader] ([CustomerID], [AddressID], [AddressStatus]) VALUES (1, 3, N'A')
INSERT [dbo].[AddrHeader] ([CustomerID], [AddressID], [AddressStatus]) VALUES (2, 4, N'A')
INSERT [dbo].[AddrHeader] ([CustomerID], [AddressID], [AddressStatus]) VALUES (3, 5, N'C')
INSERT [dbo].[AddrHeader] ([CustomerID], [AddressID], [AddressStatus]) VALUES (4, 6, N'A')
INSERT [dbo].[AddrHeader] ([CustomerID], [AddressID], [AddressStatus]) VALUES (4, 7, N'C')
INSERT [dbo].[AddrHeader] ([CustomerID], [AddressID], [AddressStatus]) VALUES (5, 8, N'A')
INSERT [dbo].[AddrHeader] ([CustomerID], [AddressID], [AddressStatus]) VALUES (6, 9, N'C')
INSERT [dbo].[AddrHeader] ([CustomerID], [AddressID], [AddressStatus]) VALUES (6, 10, N'A')
INSERT [dbo].[AddrHeader] ([CustomerID], [AddressID], [AddressStatus]) VALUES (7, 11, N'A')
INSERT [dbo].[AddrHeader] ([CustomerID], [AddressID], [AddressStatus]) VALUES (7, 12, N'C')
INSERT [dbo].[AddrHeader] ([CustomerID], [AddressID], [AddressStatus]) VALUES (8, 13, N'A')
INSERT [dbo].[AddrHeader] ([CustomerID], [AddressID], [AddressStatus]) VALUES (8, 14, N'C')
INSERT [dbo].[AddrHeader] ([CustomerID], [AddressID], [AddressStatus]) VALUES (8, 15, N'C')
INSERT [dbo].[AddrHeader] ([CustomerID], [AddressID], [AddressStatus]) VALUES (8, 16, N'C')
INSERT [dbo].[AddrHeader] ([CustomerID], [AddressID], [AddressStatus]) VALUES (8, 17, N'C')
/****** Object: Table [dbo].[AddrDetail] Script Date: 08/17/2010 01:04:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AddrDetail](
[AddressID] [int] IDENTITY(1,1) NOT NULL,
[Address1] [nvarchar](20) NULL,
[Address2] [nvarchar](20) NULL,
[City] [nvarchar](20) NULL,
[State] [nvarchar](2) NULL,
[Zip] [nvarchar](10) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[AddrDetail] ON
INSERT [dbo].[AddrDetail] ([AddressID], [Address1], [Address2], [City], [State], [Zip]) VALUES (1, N'123 Main Street', N'', N'New York', N'NY', N'12345')
INSERT [dbo].[AddrDetail] ([AddressID], [Address1], [Address2], [City], [State], [Zip]) VALUES (2, N'256 Ball Street', N'Suite 200', N'New York', N'NY', N'10070')
INSERT [dbo].[AddrDetail] ([AddressID], [Address1], [Address2], [City], [State], [Zip]) VALUES (3, N'Belview Drive', NULL, N'Milwaukee', N'WI', N'53219')
INSERT [dbo].[AddrDetail] ([AddressID], [Address1], [Address2], [City], [State], [Zip]) VALUES (4, N'Lake Michigan Drive', N'Suite 100', N'Milwaukee ', N'WI', N'53200')
INSERT [dbo].[AddrDetail] ([AddressID], [Address1], [Address2], [City], [State], [Zip]) VALUES (5, N'Lake Shore Drive', N'Suite 1005', N'Chicago', N'IL', N'60601')
INSERT [dbo].[AddrDetail] ([AddressID], [Address1], [Address2], [City], [State], [Zip]) VALUES (6, N'Chicago Blues', NULL, N'Chicago', N'IL', N'60602')
INSERT [dbo].[AddrDetail] ([AddressID], [Address1], [Address2], [City], [State], [Zip]) VALUES (7, N'1121 42nd Streeg', N'Suite 1605', N'New York', N'NY', N'10017')
INSERT [dbo].[AddrDetail] ([AddressID], [Address1], [Address2], [City], [State], [Zip]) VALUES (8, N'111 Wisconsin Ave', NULL, N'Milwaukee', N'WI', N'53218')
INSERT [dbo].[AddrDetail] ([AddressID], [Address1], [Address2], [City], [State], [Zip]) VALUES (9, N'Sun Scorched Rd', NULL, N'Phoenix', N'AZ', N'85224')
INSERT [dbo].[AddrDetail] ([AddressID], [Address1], [Address2], [City], [State], [Zip]) VALUES (10, N'Desert Plains Ave', NULL, N'Phoenix ', N'AZ', N'85228')
INSERT [dbo].[AddrDetail] ([AddressID], [Address1], [Address2], [City], [State], [Zip]) VALUES (11, N'Your Broke Street', NULL, N'Las Vegas', N'NV', N'89031')
INSERT [dbo].[AddrDetail] ([AddressID], [Address1], [Address2], [City], [State], [Zip]) VALUES (14, N'Wildest Dreams Dr', NULL, N'Las Vegas', N'NV', N'89017')
INSERT [dbo].[AddrDetail] ([AddressID], [Address1], [Address2], [City], [State], [Zip]) VALUES (15, N'Wilshire Boulevard', N'Suite 100', N'Los Angeles', N'CA', N'90001')
INSERT [dbo].[AddrDetail] ([AddressID], [Address1], [Address2], [City], [State], [Zip]) VALUES (16, N'Ocean Way Dr', NULL, N'Los Angeles ', N'CA', N'90899')
INSERT [dbo].[AddrDetail] ([AddressID], [Address1], [Address2], [City], [State], [Zip]) VALUES (17, N'Neverland', NULL, N'Los Angeles', N'CA', N'90889')
SET IDENTITY_INSERT [dbo].[AddrDetail] OFF
August 17, 2010 at 2:42 am
-----------------------------------------------------------
-- The Customer table is at the top of the hierarchy of
-- objects Customer, CustPhone, CustEmail, AddrDetail, AddrHeader
-- so use Customer as the driving table i.e. first in the FROM list.
-- Note: if a table is LEFT JOINed, then a reference to a column of the table
-- in the WHERE clause will almost certainly convert the join to an INNER JOIN.
SELECT C.CustomerID,
C.FName,
C.LName,
AH.AddressStatus,
AD.Address1,
AD.Address2,
AD.City,
AD.State,
ce.Emailaddress,
ce.EmailStatus,
cp.PhoneNo,
cp.PhoneType
FROM #Customer c
LEFT JOIN #CustPhone cp ON cp.CustomerID = c.CustomerID AND cp.PhoneType = 'H'
LEFT JOIN #CustEmail ce ON ce.CustomerID = c.CustomerID AND ce.EmailStatus = 'A'
LEFT JOIN #AddrDetail ad
INNER JOIN #AddrHeader ah ON ah.AddressID = ad.AddressID AND ah.AddressStatus = 'A'
ON ah.CustomerID = c.CustomerID
ORDER BY c.CustomerID
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 18, 2010 at 8:30 am
Thanks for the help and advice. Your solution worked fine. I am also investigating the use of APPLY.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply