Joins - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Joins

Author

Topic

dcarva
Posting Yak Master

140 Posts

Posted - 2003-07-07 : 14:40:01

Hello, I have two tables. (Accounts and Incidents) I need to display each account ONLY ONCE, and list the LATEST support call header next to it. I had to use a LEFT OUTER JOIN to give me all accounts, even if there are no support calls. However, the OUTER JOIN causes the accounts to be listed more than once. If there are 4 incidents, then the account name will be listed four times. So I currently have it like this:

ABC - 1/1/2003 - Revamped website
ABC - 12/2/2002 - Fixed machine
ABC - 11/1/2002 - Added patches
XYZ - No Calls
MSC - 1/1/2001 - Updated server

I need my stored procedure to return them like this:

ABC - 1/1/2003 - Revamped website
XYZ - No Calls
MSC - 1/1/2001 - Updated server

How can I update the sp below to do this?

CREATE PROCEDURE sp_ViewAccounts
AS
SET NOCOUNT ON
SELECT a.CustomerId, a.CompanyName, ISNULL( i.[Date], ' '), i.Comments, i.[Date]
FROM Accounts AS a
LEFT OUTER JOIN Incidents AS i
ON i.CustomerID = a.customerid
LEFT OUTER JOIN
(
SELECT CustomerID, MAX (Date) as Date
FROM Incidents
GROUP BY CustomerID
) x
ON x.CustomerID = i.CustomerID
AND x.Date = i.Date
ORDER BY a.CustomerId, i.[Date] DESC
SET NOCOUNT OFF
GO

Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-07 : 15:09:35

Join or no join, it doesn't matter:


USE Northwind
GO

  SELECT OrderId, Max(OrderDate) 
    FROM Orders
GROUP BY OrderId
GO

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-07 : 15:17:06

Something with a little more meaning:


USE Northwind
GO


INSERT INTO Customers (CustomerId, CompanyName) SELECT 'Brett', 'Brett Beer Works'
INSERT INTO Orders (CustomerId) SELECT 'Brett'
GO

  SELECT CustomerId, Max(OrderDate) As LastOrder
    FROM Orders
GROUP BY CustomerId
ORDER BY CustomerId
GO


DELETE FROM Orders Where CustomerId = 'Brett'
GO
DELETE FROM Customer Where CustomerId = 'Brett'
GO

Brett

8-)

dcarva
Posting Yak Master

140 Posts

Posted - 2003-07-07 : 15:20:26

Your example works, but with the left join causes the item to appear twice. In my SP, you can see that I am using SELECT with MAX(Date).

Thanks!

Edited by - dcarva on 07/07/2003 15:29:20

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-07 : 16:04:38

dcarva --

Try this:



select
  a.CustomerId, a.CompanyName, ISNULL( i.[Date], ' '), i.Comments, i.[Date] 
from
  accounts a
left outer join
  (
    select 
       i.*
    from
       incidents i
    inner join
        (
         select 
             customerId, max(date) as date
         from 
             incidents
         group by 
             customer ID
         ) a
     on 
         a.customerID = i.customerID and
         a.Date = i.date
  ) i
on 
a.customerID = i.customerID

- Jeff

dcarva
Posting Yak Master

140 Posts

Posted - 2003-07-07 : 16:13:59

That worked!!! I will study that logic.
Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-07 : 16:25:08

quote:

Your example works, but with the left join causes the item to appear twice. In my SP, you can see that I am using SELECT with MAX(Date).

My inference engine must be broken today (even though you spelled it out).

What Jeff gave (as you already know) is how to do what you want.

What he's doing is first finding the max date in the inner query, then based on a correlation on the date and key, he the gets the detail rows.

Just a word of caution, in the highly unlikely (well I guess it's subjective) event that you get the same datetime value, you could get n rows back for that key.

Ain't that so Jeff?

Brett

8-)

Edited by - x002548 on 07/07/2003 16:26:10

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-07 : 17:39:26

yes, hopefully customerID/Date is the primary key of the incidents tables (or at least a unique constraint). otherwise as you mention, you will get repeating rows if there are two incidents for the same customer for the same date.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources