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
 INNER JOIN and Multiplied Results

Author  Topic 

BobRoberts
Posting Yak Master

109 Posts
Posted - 2011-12-12 : 10:42:26
[code]
In this example I need to find the top 2 customers with the most transactions and display the associated customer name.

SELECT TOP 2 TBL_SLS_TRANSACT.customer_id, TBL_CUSTOMERS.customer_name, COUNT(TBL_SLS_TRANSACT.customer_id) AS NumberOfTransactions
FROM TBL_SLS_TRANSACT
INNER JOIN TBL_CUSTOMERS ON TBL_SLS_TRANSACT.customer_id = TBL_CUSTOMERS.customer_id
GROUP BY TBL_SLS_TRANSACT.customer_id, TBL_CUSTOMERS.customer_name
ORDER BY COUNT(TBL_SLS_TRANSACT.customer_id) DESC

-------------------------------------------------
TBL_SLS_TRANSACT:

customer_id|transaction_id|transaction_amt
111111 728172 22.4000
222222 282838 180.9100
222222 372727 55.1000
222222 412123 18.5200
333333 573838 331.2500
333333 459282 44.7100

TBL_CUSTOMERS:

customer_id|customer_name| visit_date
111111 Jason Riverson 2011-05-30 00:00:00
222222 Tiara Pelican 2011-09-12 00:00:00
333333 Sid Seleasa 2011-07-05 00:00:00

Running the above script yields:

customer_id|customer_name| NumberOfTransactions
222222 Tiara Pelican 3
333333 Sid Seleasa 2


But when the TBL_CUSTOMERS changes to...

customer_id|customer_name| visit_date
111111 Jason Riverson 2011-05-30 00:00:00
222222 Tiara Pelican 2011-09-12 00:00:00
333333 Sid Seleasa 2011-07-05 00:00:00
222222 Tiara Pelican 2011-10-17 00:00:00
222222 Tiara Pelican 2011-11-03 00:00:00
222222 Tiara Pelican 2011-12-20 00:00:00


...the script yields:

customer_id|customer_name| NumberOfTransactions
222222 Tiara Pelican 12
333333 Sid Seleasa 2

I realize why this is happening, and know that it's a common difficulty, and have tried a few things, but have been unable to find a specific adjustment that will display the

customer name while displaying the correct number of transactions for each customer. The customer name will never vary with each visit, and so I don't really care which

customer name is displayed. It's too bad SQL doesn't have an INNER SINGLE JOIN - that would make sense and be very helpful.

Here's a script to create the two tables, followed by a script to create the second version of the TBL_CUSTOMERS table.

--------------------------------------
USE [Northwind]
GO
/****** Object: Table [dbo].[TBL_SLS_TRANSACT] Script Date: 12/09/2011 14:25:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TBL_SLS_TRANSACT](
[customer_id] [varchar](6) NULL,
[transaction_id] [varchar](10) NULL,
[transaction_amt] [money] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[TBL_SLS_TRANSACT] ([customer_id], [transaction_id], [transaction_amt]) VALUES (N'111111', N'728172', 22.4000)
INSERT [dbo].[TBL_SLS_TRANSACT] ([customer_id], [transaction_id], [transaction_amt]) VALUES (N'222222', N'282838', 180.9100)
INSERT [dbo].[TBL_SLS_TRANSACT] ([customer_id], [transaction_id], [transaction_amt]) VALUES (N'222222', N'372727', 55.1000)
INSERT [dbo].[TBL_SLS_TRANSACT] ([customer_id], [transaction_id], [transaction_amt]) VALUES (N'222222', N'412123', 18.5200)
INSERT [dbo].[TBL_SLS_TRANSACT] ([customer_id], [transaction_id], [transaction_amt]) VALUES (N'333333', N'573838', 331.2500)
INSERT [dbo].[TBL_SLS_TRANSACT] ([customer_id], [transaction_id], [transaction_amt]) VALUES (N'333333', N'459282', 44.7100)
/****** Object: Table [dbo].[TBL_CUSTOMERS] Script Date: 12/09/2011 14:25:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TBL_CUSTOMERS](
[customer_id] [varchar](6) NULL,
[customer_name] [varchar](20) NULL,
[visit_date] [smalldatetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[TBL_CUSTOMERS] ([customer_id], [customer_name], [visit_date]) VALUES (N'111111', N'Jason Riverson', CAST(0x9EF30000 AS SmallDateTime))
INSERT [dbo].[TBL_CUSTOMERS] ([customer_id], [customer_name], [visit_date]) VALUES (N'222222', N'Tiara Pelican', CAST(0x9F5C0000 AS SmallDateTime))
INSERT [dbo].[TBL_CUSTOMERS] ([customer_id], [customer_name], [visit_date]) VALUES (N'333333', N'Sid Seleasa', CAST(0x9F170000 AS SmallDateTime))

---------------------------------------
USE [Northwind]
GO
/****** Object: Table [dbo].[TBL_CUSTOMERS] Script Date: 12/09/2011 16:17:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TBL_CUSTOMERS](
[customer_id] [varchar](6) NULL,
[customer_name] [varchar](20) NULL,
[visit_date] [smalldatetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[TBL_CUSTOMERS] ([customer_id], [customer_name], [visit_date]) VALUES (N'111111', N'Jason Riverson', CAST(0x9EF30000 AS SmallDateTime))
INSERT [dbo].[TBL_CUSTOMERS] ([customer_id], [customer_name], [visit_date]) VALUES (N'222222', N'Tiara Pelican', CAST(0x9F5C0000 AS SmallDateTime))
INSERT [dbo].[TBL_CUSTOMERS] ([customer_id], [customer_name], [visit_date]) VALUES (N'333333', N'Sid Seleasa', CAST(0x9F170000 AS SmallDateTime))
INSERT [dbo].[TBL_CUSTOMERS] ([customer_id], [customer_name], [visit_date]) VALUES (N'222222', N'Tiara Pelican', CAST(0x9F7F0000 AS SmallDateTime))
INSERT [dbo].[TBL_CUSTOMERS] ([customer_id], [customer_name], [visit_date]) VALUES (N'222222', N'Tiara Pelican', CAST(0x9F900000 AS SmallDateTime))
INSERT [dbo].[TBL_CUSTOMERS] ([customer_id], [customer_name], [visit_date]) VALUES (N'222222', N'Tiara Pelican', CAST(0x9FBF0000 AS SmallDateTime))
--------------------------------------


[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-12-12 : 10:45:38
for new scenario make code like

SELECT TOP 2 TBL_SLS_TRANSACT.customer_id, c.customer_name, COUNT(TBL_SLS_TRANSACT.customer_id) AS NumberOfTransactions
FROM TBL_SLS_TRANSACT 
INNER JOIN (SELECT customer_id,customer_name
            FROM TBL_CUSTOMERS 
            GROUP BY customer_id,customer_name)c
ON TBL_SLS_TRANSACT.customer_id = c.customer_id
GROUP BY TBL_SLS_TRANSACT.customer_id, c.customer_name
ORDER BY COUNT(TBL_SLS_TRANSACT.customer_id) DESC


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

BobRoberts
Posting Yak Master

109 Posts
Posted - 2011-12-12 : 11:31:36
Thanks very much - this is exactly what I needed. I attempted something similar, but didn't follow through with the alias "c" as you have done.


quote:
Originally posted by visakh16

for new scenario make code like

SELECT TOP 2 TBL_SLS_TRANSACT.customer_id, c.customer_name, COUNT(TBL_SLS_TRANSACT.customer_id) AS NumberOfTransactions
FROM TBL_SLS_TRANSACT 
INNER JOIN (SELECT customer_id,customer_name
            FROM TBL_CUSTOMERS 
            GROUP BY customer_id,customer_name)c
ON TBL_SLS_TRANSACT.customer_id = c.customer_id
GROUP BY TBL_SLS_TRANSACT.customer_id, c.customer_name
ORDER BY COUNT(TBL_SLS_TRANSACT.customer_id) DESC


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-12-12 : 11:37:02
no problem
you're wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

BobRoberts
Posting Yak Master

109 Posts
Posted - 2011-12-19 : 12:53:15
[code]I got an additional twist on the same problem. I need to display the customer's most recent telephone number from the TBL_CUSTOMER_TELEPHONE_CHANGES table, which can be represented as this:

TBL_CUSTOMER_TELEPHONE_CHANGES:

customer_id|date_of_change|telephone_number

111111 2010-03-12 6305551234
111111 2010-09-01 3128728483
222222 2010-07-07 3178872737
333333 2010-10-01 6307772323
333333 2010-05-05 3171234567


I thought that intuitively I could add another INNER JOIN clause immediately after the first, but the great SQL god will not allow that, complaining of various syntac errors with whatever I try.
Below is the script that works that I need to modify to display the latest telephone number for the customer:

SELECT TOP 2 TBL_SLS_TRANSACT.customer_id, c.customer_name, COUNT(TBL_SLS_TRANSACT.customer_id) AS NumberOfTransactions
FROM TBL_SLS_TRANSACT
INNER JOIN (SELECT customer_id,customer_name
FROM TBL_CUSTOMERS
GROUP BY customer_id,customer_name)c

ON TBL_SLS_TRANSACT.customer_id = c.customer_id
GROUP BY TBL_SLS_TRANSACT.customer_id, c.customer_name
ORDER BY COUNT(TBL_SLS_TRANSACT.customer_id) DESC


Here is the script used to create the TBL_CUSTOMER_TELEPHONE_CHANGES table.

USE [Northwind]
GO
/****** Object: Table [dbo].[TBL_CUSTOMER_TELEPHONE_CHANGES] Script Date: 12/19/2011 10:28:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TBL_CUSTOMER_TELEPHONE_CHANGES](
[Customer_ID] [varchar](6) NULL,
[Date_of_Change] [smalldatetime] NULL,
[Telephone_number] [nchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[TBL_CUSTOMER_TELEPHONE_CHANGES] ([Customer_ID], [Date_of_Change], [Telephone_number]) VALUES (N'111111', CAST(0x9D370000 AS SmallDateTime), N'6305551234')
INSERT [dbo].[TBL_CUSTOMER_TELEPHONE_CHANGES] ([Customer_ID], [Date_of_Change], [Telephone_number]) VALUES (N'111111', CAST(0x9DE40000 AS SmallDateTime), N'3128728483')
INSERT [dbo].[TBL_CUSTOMER_TELEPHONE_CHANGES] ([Customer_ID], [Date_of_Change], [Telephone_number]) VALUES (N'222222', CAST(0x9DAC0000 AS SmallDateTime), N'3178872737')
INSERT [dbo].[TBL_CUSTOMER_TELEPHONE_CHANGES] ([Customer_ID], [Date_of_Change], [Telephone_number]) VALUES (N'333333', CAST(0x9E02038F AS SmallDateTime), N'6307772323')
INSERT [dbo].[TBL_CUSTOMER_TELEPHONE_CHANGES] ([Customer_ID], [Date_of_Change], [Telephone_number]) VALUES (N'333333', CAST(0x9D6D038F AS SmallDateTime), N'3171234567')[/code]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-12-19 : 13:04:57
[code]
SELECT TOP 2 TBL_SLS_TRANSACT.customer_id, c.customer_name, COUNT(TBL_SLS_TRANSACT.customer_id) AS NumberOfTransactions,
t.telephone_number
FROM TBL_SLS_TRANSACT
INNER JOIN (SELECT customer_id,customer_name
FROM TBL_CUSTOMERS
GROUP BY customer_id,customer_name)c
ON TBL_SLS_TRANSACT.customer_id = c.customer_id
INNER JOIN TBL_CUSTOMER_TELEPHONE_CHANGES t
ON t.customer_id = c.customer_id
INNER JOIN (SELECT customer_id,MAX(Date_of_Change) AS LatestDate
FROM TBL_CUSTOMER_TELEPHONE_CHANGES
GROUP BY customer_id)t1
ON t1.customer_i1d = t.customer_id
AND t1.LatestDate = t.Date_of_Change
GROUP BY TBL_SLS_TRANSACT.customer_id, c.customer_name
ORDER BY COUNT(TBL_SLS_TRANSACT.customer_id) DESC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

BobRoberts
Posting Yak Master

109 Posts
Posted - 2011-12-20 : 16:44:34
Thanks for your response. When I run it, I get: "Column 'TBL_CUSTOMER_TELEPHONE_CHANGES.Telephone_number' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

When I put the field "TBL_CUSTOMER_TELEPHONE_CHANGES.Telephone_number'"
at the end of the final GROUP BY clause, I get this error: "The multi-part identifier "TBL_CUSTOMER_TELEPHONE_CHANGES.Telephone_number" could not be bound.

quote:
Originally posted by visakh16


SELECT TOP 2 TBL_SLS_TRANSACT.customer_id, c.customer_name, COUNT(TBL_SLS_TRANSACT.customer_id) AS NumberOfTransactions,
t.telephone_number
FROM TBL_SLS_TRANSACT 
INNER JOIN (SELECT customer_id,customer_name
            FROM TBL_CUSTOMERS 
            GROUP BY customer_id,customer_name)c
ON TBL_SLS_TRANSACT.customer_id = c.customer_id
INNER JOIN TBL_CUSTOMER_TELEPHONE_CHANGES t
ON t.customer_id  = c.customer_id 
INNER JOIN (SELECT customer_id,MAX(Date_of_Change) AS LatestDate
            FROM TBL_CUSTOMER_TELEPHONE_CHANGES 
            GROUP BY customer_id)t1
ON t1.customer_i1d = t.customer_id 
AND t1.LatestDate = t.Date_of_Change
GROUP BY TBL_SLS_TRANSACT.customer_id, c.customer_name
ORDER BY COUNT(TBL_SLS_TRANSACT.customer_id) DESC


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

BobRoberts
Posting Yak Master

109 Posts
Posted - 2011-12-21 : 11:21:09
I can get it to work, but the phone number it selects is not the one associated with the latest date (for Sid Seleasa):


INNER JOIN (SELECT customer_id,customer_name
            FROM TBL_CUSTOMERS 
            GROUP BY customer_id,customer_name)c
ON TBL_SLS_TRANSACT.customer_id = c.customer_id

LEFT JOIN  
( 
SELECT MAX(Date_of_Change) AS LatestDate, Date_Of_Change, customer_id, telephone_number
            FROM TBL_CUSTOMER_TELEPHONE_CHANGES 
            GROUP BY Date_of_Change, customer_id, telephone_number
  ) CTC 
ON CTC.customer_id = C.customer_id 
AND CTC.LatestDate = CTC.Date_of_Change

GROUP BY TBL_SLS_TRANSACT.customer_id, c.customer_name, CTC.telephone_number
ORDER BY COUNT(TBL_SLS_TRANSACT.customer_id) DESC
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-12-21 : 11:45:17
please use query as suggested you should join it twice like below


...
INNER JOIN TBL_CUSTOMER_TELEPHONE_CHANGES t
ON t.customer_id  = c.customer_id 
INNER JOIN (SELECT customer_id,MAX(Date_of_Change) AS LatestDate
            FROM TBL_CUSTOMER_TELEPHONE_CHANGES 
            GROUP BY customer_id)t1
ON t1.customer_i1d = t.customer_id 
AND t1.LatestDate = t.Date_of_Change
...


one for getting details and one to filter latest out of a group

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

BobRoberts
Posting Yak Master

109 Posts
Posted - 2011-12-28 : 10:19:36
quote:
Originally posted by visakh16

please use query as suggested you should join it twice like below


...
INNER JOIN TBL_CUSTOMER_TELEPHONE_CHANGES t
ON t.customer_id  = c.customer_id 
INNER JOIN (SELECT customer_id,MAX(Date_of_Change) AS LatestDate
            FROM TBL_CUSTOMER_TELEPHONE_CHANGES 
            GROUP BY customer_id)t1
ON t1.customer_i1d = t.customer_id 
AND t1.LatestDate = t.Date_of_Change
...


one for getting details and one to filter latest out of a group

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






The following seems to work. Is this what you had in mind?

SELECT TOP 2 TBL_SLS_TRANSACT.customer_id, c.customer_name,
t.telephone_number, COUNT(TBL_SLS_TRANSACT.customer_id) AS NumberOfTransactions
FROM TBL_SLS_TRANSACT

INNER JOIN (SELECT customer_id,customer_name
FROM TBL_CUSTOMERS
GROUP BY customer_id,customer_name)c
ON TBL_SLS_TRANSACT.customer_id = c.customer_id

INNER JOIN TBL_CUSTOMER_TELEPHONE_CHANGES t
ON t.customer_id = c.customer_id
INNER JOIN (SELECT customer_id,MAX(Date_of_Change) AS LatestDate
FROM TBL_CUSTOMER_TELEPHONE_CHANGES
GROUP BY customer_id)t1
ON t1.customer_id = t.customer_id
AND t1.LatestDate = t.Date_of_Change

GROUP BY TBL_SLS_TRANSACT.customer_id, c.customer_name, t.telephone_number
ORDER BY COUNT(TBL_SLS_TRANSACT.customer_id) DESC
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2011-12-28 : 11:14:59
WAIT!!! Do you KNOW who Bob Roberts IS?

One of my all time favorite movies

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2011-12-28 : 11:22:08
quote:
Originally posted by X002548

WAIT!!! Do you KNOW who Bob Roberts IS?

One of my all time favorite movies

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/





Vote first. Ask questions later.


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-12-28 : 11:36:59
quote:
Originally posted by BobRoberts

quote:
Originally posted by visakh16

please use query as suggested you should join it twice like below


...
INNER JOIN TBL_CUSTOMER_TELEPHONE_CHANGES t
ON t.customer_id  = c.customer_id 
INNER JOIN (SELECT customer_id,MAX(Date_of_Change) AS LatestDate
            FROM TBL_CUSTOMER_TELEPHONE_CHANGES 
            GROUP BY customer_id)t1
ON t1.customer_i1d = t.customer_id 
AND t1.LatestDate = t.Date_of_Change
...


one for getting details and one to filter latest out of a group

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






The following seems to work. Is this what you had in mind?

SELECT TOP 2 TBL_SLS_TRANSACT.customer_id, c.customer_name,
t.telephone_number, COUNT(TBL_SLS_TRANSACT.customer_id) AS NumberOfTransactions
FROM TBL_SLS_TRANSACT

INNER JOIN (SELECT customer_id,customer_name
FROM TBL_CUSTOMERS
GROUP BY customer_id,customer_name)c
ON TBL_SLS_TRANSACT.customer_id = c.customer_id

INNER JOIN TBL_CUSTOMER_TELEPHONE_CHANGES t
ON t.customer_id = c.customer_id
INNER JOIN (SELECT customer_id,MAX(Date_of_Change) AS LatestDate
FROM TBL_CUSTOMER_TELEPHONE_CHANGES
GROUP BY customer_id)t1
ON t1.customer_id = t.customer_id
AND t1.LatestDate = t.Date_of_Change

GROUP BY TBL_SLS_TRANSACT.customer_id, c.customer_name, t.telephone_number
ORDER BY COUNT(TBL_SLS_TRANSACT.customer_id) DESC

Indeed
thats what i meant


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site