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
BangaloreCustomer SupportMr Pink
BangaloreOperations Mr Pink
BangaloreMaintenance Mr Pink
BangaloreQuality Mr Pink
BangaloreCustomer SupportMr Green
BangaloreOperations Mr Green
BangaloreMaintenance Mr Green
BangaloreQuality Mr Green
BangaloreCustomer SupportMr White
BangaloreOperations 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
BangaloreCustomer SupportMr Pink
BangaloreOperations Mr Pink
BangaloreMaintenance Mr Pink
BangaloreQuality Mr Pink
BangaloreCustomer SupportMr Green
BangaloreOperations Mr Green
BangaloreMaintenance Mr Green
BangaloreQuality Mr Green
BangaloreCustomer SupportMr White
BangaloreOperations 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
IndiaOperations MrWhite
IndiaQuality MrWhite
IndiaPerformance MrWhite
IndiaOperationBurma MrWhite
IndiaQuaityBurma MrWhite
IndiaPerformanceBurma MrWhite
IndiaOperations MrPink
IndiaQuality MrPink
IndiaPerformance MrPink
IndiaOperationBurma MrPink
IndiaQuaityBurma MrPink
IndiaPerformanceBurma MrPink
IndiaOperations Mr Green
IndiaQuality Mr Green
IndiaPerformance Mr Green
IndiaOperationBurma Mr Green
IndiaQuaityBurma Mr Green
IndiaPerformanceBurma Mr Green
BurmaOperations Mr Black
BurmaQuality Mr Black
BurmaPerformance Mr Black
BurmaOperationBurma Mr Black
BurmaQuaityBurma Mr Black
BurmaPerformanceBurma Mr Black
BurmaOperations Mr Yellow
BurmaQuality Mr Yellow
BurmaPerformance Mr Yellow
BurmaOperationBurma Mr Yellow
BurmaQuaityBurma Mr Yellow
BurmaPerformanceBurma 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