February 20, 2015 at 12:18 pm
I have to select from three tables as laid out below
Customer
CustomerID,CustomerName,CustomerLocationID,CustomerAreaID
Which has data as below
1,Mrpink, 1,1
CustomerLocation
CustomerLcoationID, CustomerLocationName
Which has data as below
1,Banglore
CustomerArea
CustomerLocationId,CustomerAreaID,CustomerAreaName
1,1,CustomerSupport
1,2,Operations
1,2,Maintenance
To join the tables i use the query below
Select CL.CustomerLocationName as Location,CA.CustomerAreaName Area,C.CustomerName as name
from customer C inner join CustomerLocation CL on C.CustomerLocationID = CL.CustomerLocationID
inner join CustomerArea CA on CL.CustomerLocationID = CA.CustomerAreaName
But the query above returns duplicate results as seen below.
Location Area Name
Bangalore Customer Support Mr Pink
Bangalore Operations Mr Pink
Bangalore Maintenance Mr Pink
Bangalore Quality Mr Pink
Bangalore Customer Support Mr Green
Bangalore Operations Mr Green
Bangalore Maintenance Mr Green
Bangalore Quality Mr Green
Bangalore Customer Support Mr White
Bangalore Operations Mr White
*these names were directly plagiarized from Reservoir dogs
I used a left outer join on the area and i got the same result.
Thanks for the help
February 20, 2015 at 12:45 pm
SQLTestUser (2/20/2015)
I have to select from three tables as laid out belowCustomer
CustomerID,CustomerName,CustomerLocationID,CustomerAreaID
Which has data as below
1,Mrpink, 1,1
CustomerLocation
CustomerLcoationID, CustomerLocationName
Which has data as below
1,Banglore
CustomerArea
CustomerLocationId,CustomerAreaID,CustomerAreaName
1,1,CustomerSupport
1,2,Operations
1,2,Maintenance
To join the tables i use the query below
Select CL.CustomerLocationName as Location,CA.CustomerAreaName Area,C.CustomerName as name
from customer C inner join CustomerLocation CL on C.CustomerLocationID = CL.CustomerLocationID
inner join CustomerArea CA on CL.CustomerLocationID = CA.CustomerAreaName
But the query above returns duplicate results as seen below.
Location Area Name
Bangalore Customer Support Mr Pink
Bangalore Operations Mr Pink
Bangalore Maintenance Mr Pink
Bangalore Quality Mr Pink
Bangalore Customer Support Mr Green
Bangalore Operations Mr Green
Bangalore Maintenance Mr Green
Bangalore Quality Mr Green
Bangalore Customer Support Mr White
Bangalore Operations Mr White
*these names were directly plagiarized from Reservoir dogs
I used a left outer join on the area and i got the same result.
Thanks for the help
I believe part of the problem is
inner join CustomerArea CA on CL.CustomerLocationID = CA.CustomerAreaName
try
inner join CustomerArea CA on C.CustomerLocationID = CA.CustomerLocationID AND C.CustomerAreaID = CA.CustomerAreaID
February 20, 2015 at 12:52 pm
That did not do it
February 20, 2015 at 1:28 pm
I'm wondering about the design. If, in CustomerArea, the CustomerAreaID is the primary key, why are there duplicates?
Also, should the CustomerAreaID be stored in the Customer table? If an area is a group of locations, shouldn't the AreaID be in the CustomerLocation table?
February 20, 2015 at 1:32 pm
not allowed to change the structure, wish i could, A location can have multiple areas, a customer can be associated to only one location and one area. The concept is that a company can have a employee in India, or Germany etc and at India the company could have different areas for the same location, similarly for Germany.
February 20, 2015 at 1:39 pm
SQLTestUser (2/20/2015)
not allowed to change the structure, wish i could, A location can have multiple areas, a customer can be associated to only one location and one area. The concept is that a company can have a employee in India, or Germany etc and at India the company could have different areas for the same location, similarly for Germany.
What would help more than anything is if you could post the ddl and some sample data for this along with the desired results. The query itself probably isn't that bad but trying to help you code against structures we can't see is incredibly difficult.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 20, 2015 at 1:42 pm
Sean Lange (2/20/2015)
SQLTestUser (2/20/2015)
not allowed to change the structure, wish i could, A location can have multiple areas, a customer can be associated to only one location and one area. The concept is that a company can have a employee in India, or Germany etc and at India the company could have different areas for the same location, similarly for Germany.What would help more than anything is if you could post the ddl and some sample data for this along with the desired results. The query itself probably isn't that bad but trying to help you code against structures we can't see is incredibly difficult.
Agreed. There's just something I'm not seeing here. The structure seems weird to me, but I suppose it could it could be the way I'm looking at it.
February 20, 2015 at 1:52 pm
Ed Wagner (2/20/2015)
Sean Lange (2/20/2015)
SQLTestUser (2/20/2015)
not allowed to change the structure, wish i could, A location can have multiple areas, a customer can be associated to only one location and one area. The concept is that a company can have a employee in India, or Germany etc and at India the company could have different areas for the same location, similarly for Germany.What would help more than anything is if you could post the ddl and some sample data for this along with the desired results. The query itself probably isn't that bad but trying to help you code against structures we can't see is incredibly difficult.
Agreed. There's just something I'm not seeing here. The structure seems weird to me, but I suppose it could it could be the way I'm looking at it.
It does seem that there is badly mangled many to many relationship in there but maybe we will figure that out once we have something to work with.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 20, 2015 at 2:04 pm
/****** Object: Table [dbo].[CustomerArea] Script Date: 2/20/2015 1:59:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CustomerArea](
[CustoemerLocationID] [smallint] NULL,
[CustomerAreaID] [smallint] NULL,
[CustomerAreaName] [nvarchar](50) NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CustomerInfo](
[CustomerfirstName] [nvarchar](200) NULL,
[CustomerLastName] [nvarchar](200) NULL,
[LocationID] [smallint] NULL,
[AreaID] [smallint] NULL,
[CustomerID] [nchar](10) NOT NULL,
CONSTRAINT [PK_CustomerInfo] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[CustomerLocationName] Script Date: 2/20/2015 1:59:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CustomerLocationName](
[LocationID] [smallint] NULL,
[LocationName] [nvarchar](50) NULL,
[Active] [bit] NULL
) ON [PRIMARY]
GO
/****** data Customer LocationName ******/
INSERT [dbo].[CustomerLocationName] ([LocationID], [LocationName], [Active]) VALUES (1, N'India', 1)
INSERT [dbo].[CustomerLocationName] ([LocationID], [LocationName], [Active]) VALUES (2, N'Burma', 1)
/****** data Customer Info ******/
INSERT [dbo].[CustomerInfo] ([CustomerfirstName], [CustomerLastName], [LocationID], [AreaID], [CustomerID]) VALUES (N'Mr', N'White', 1, 1, N'1 ')
INSERT [dbo].[CustomerInfo] ([CustomerfirstName], [CustomerLastName], [LocationID], [AreaID], [CustomerID]) VALUES (N'Mr', N'Pink', 1, 2, N'2 ')
INSERT [dbo].[CustomerInfo] ([CustomerfirstName], [CustomerLastName], [LocationID], [AreaID], [CustomerID]) VALUES (N'Mr ', N'Green', 1, 3, N'3 ')
INSERT [dbo].[CustomerInfo] ([CustomerfirstName], [CustomerLastName], [LocationID], [AreaID], [CustomerID]) VALUES (N'Mr ', N'Black', 2, 1, N'4 ')
INSERT [dbo].[CustomerInfo] ([CustomerfirstName], [CustomerLastName], [LocationID], [AreaID], [CustomerID]) VALUES (N'Mr ', N'Yellow', 2, 2, N'5 ')
/****** data Customer Area ******/
INSERT [dbo].[CustomerArea] ([CustoemerLocationID], [CustomerAreaID], [CustomerAreaName]) VALUES (1, 1, N'Operations')
INSERT [dbo].[CustomerArea] ([CustoemerLocationID], [CustomerAreaID], [CustomerAreaName]) VALUES (1, 2, N'Quality')
INSERT [dbo].[CustomerArea] ([CustoemerLocationID], [CustomerAreaID], [CustomerAreaName]) VALUES (1, 3, N'Performance')
INSERT [dbo].[CustomerArea] ([CustoemerLocationID], [CustomerAreaID], [CustomerAreaName]) VALUES (2, 1, N'OperationBurma')
INSERT [dbo].[CustomerArea] ([CustoemerLocationID], [CustomerAreaID], [CustomerAreaName]) VALUES (2, 2, N'QuaityBurma')
INSERT [dbo].[CustomerArea] ([CustoemerLocationID], [CustomerAreaID], [CustomerAreaName]) VALUES (2, 3, N'PerformanceBurma')
/****** Query i Tried to no avail ******/
SELECT LocationName, CustomerAReaName, (CustomerFirstNAme + CustomerLastName) AS Name
FROM CustomerInfo INNER JOIN CustomerLocationName ON CustomerInfo.LocationID = CustomerLocationName.LocationID
LEFT outer JOIN CustomerArea ON CustomerArea.CustoemerLocationID = CustomerArea.CustoemerLocationID
and the result was as shown below
India Operations MrWhite
India Quality MrWhite
India Performance MrWhite
India OperationBurma MrWhite
India QuaityBurma MrWhite
India PerformanceBurma MrWhite
India Operations MrPink
India Quality MrPink
India Performance MrPink
India OperationBurma MrPink
India QuaityBurma MrPink
India PerformanceBurma MrPink
India Operations Mr Green
India Quality Mr Green
India Performance Mr Green
India OperationBurma Mr Green
India QuaityBurma Mr Green
India PerformanceBurma Mr Green
Burma Operations Mr Black
Burma Quality Mr Black
Burma Performance Mr Black
Burma OperationBurma Mr Black
Burma QuaityBurma Mr Black
Burma PerformanceBurma Mr Black
Burma Operations Mr Yellow
Burma Quality Mr Yellow
Burma Performance Mr Yellow
Burma OperationBurma Mr Yellow
Burma QuaityBurma Mr Yellow
Burma PerformanceBurma Mr Yellow
February 20, 2015 at 2:08 pm
OK. We are close. The CustomerArea table is missing. Also, cool that you posted a query that doesn't work but what is the output you want from this data?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 20, 2015 at 3:34 pm
Your LEFT JOIN condition has a problem as both columns belong to CustomerArea. Additional to that, your CustomerArea key seems to consist on 2 columns and you're using just one.
Try this:
SELECT LocationName,
CustomerAReaName,
(CustomerFirstNAme + CustomerLastName) AS Name
FROM CustomerInfo CI
INNER JOIN CustomerLocationName CLN ON CI.LocationID = CLN.LocationID
LEFT OUTER JOIN CustomerArea CA ON CI.LocationID = CA.CustoemerLocationID
AND CI.AreaID = CA.CustomerAreaID
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply