Help with a Query

A simple sales activity tracking system. We collect information about
Clients, and sales Activity related to the clients.
Here's some simplified DDL and some insert statements
CREATE TABLE [Client] (
Id [int] NOT NULL ,
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
Id
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
CREATE TABLE [Activity] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[ClientId] [int] NOT NULL ,
[UserIdEnteredBy] [int] NOT NULL ,
[DateofActivity] [datetime] NOT NULL ,
CONSTRAINT [PK_Activity] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_Activity_Client] FOREIGN KEY
(
[ClientId]
) REFERENCES [Client] (
[Id]
)
) ON [PRIMARY]
END
GO
INSERT INTO Client (Id) VALUES (1)
INSERT INTO Client (Id) VALUES (2)
INSERT INTO Activity ([ClientId], [UserIdEnteredBy], [DateofActivity])
VALUES (1, 1, '01/01/2005')
INSERT INTO Activity ([ClientId], [UserIdEnteredBy], [DateofActivity])
VALUES (1, 2, '02/01/2005')
INSERT INTO Activity ([ClientId], [UserIdEnteredBy], [DateofActivity])
VALUES (1, 2, '03/01/2005')
INSERT INTO Activity ([ClientId], [UserIdEnteredBy], [DateofActivity])
VALUES (1, 1, '04/01/2005')
I want a list of all Clients, and the LastContacted Date, and who the
user who LastContacted them was.
Here's what I have so far:
SELECT
C.Id,
A.[UserIdEnteredBy] AS LastContactedBy,
A.[DateofActivity] AS LastContacted
FROM
Client C
LEFT OUTER JOIN Activity A ON C.Id = A.[ClientId]
JOIN (SELECT ClientId, Max(Id) AS MaxActivityId From Activity GROUP BY
ClientId) L
ON A.Id = L.MaxActivityId AND A.ClientId = L.ClientId
This gives me:
Id LastContactedBy LastContacted
---
1 1 2005-04-01 00:00:00.000
However, I want to return:
Id LastContactedBy LastContacted
---
1 1 2005-04-01 00:00:00.000
2 NULL NULL
This has got to be something with my joins, but I can't figure it out.
Thanks!Hi,
Try this instead...
Select Client.ID,
Activity.UserIDEnteredBy,
Activity.DateOfActivity

>From Client
Left Outer Join Activity on Client.ID = Activity.ClientID
Where Activity.DateOfActivity = (select Max(Activity2.dateofactivity)
from Activity Activity2
where Activity2.ClientID = Client.ID)
or Activity.UserIDEnteredBy IS Null
HTH
Barry|||Try
select c.*,tmp.* from client c left join (SELECT ClientId, Max(Id) AS
MaxActivityId From Activity GROUP BY
ClientId) tmp on c.id=tmp.clientid
Cheers,
JP
----
A program is a device used to convert,
data into error messages
----
<george.durzi@.gmail.com> wrote in message
news:1126291592.558676.297110@.g49g2000cwa.googlegroups.com...
>A simple sales activity tracking system. We collect information about
> Clients, and sales Activity related to the clients.
> Here's some simplified DDL and some insert statements
> CREATE TABLE [Client] (
> Id [int] NOT NULL ,
> CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
> (
> Id
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> END
> GO
> CREATE TABLE [Activity] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [ClientId] [int] NOT NULL ,
> [UserIdEnteredBy] [int] NOT NULL ,
> [DateofActivity] [datetime] NOT NULL ,
> CONSTRAINT [PK_Activity] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Activity_Client] FOREIGN KEY
> (
> [ClientId]
> ) REFERENCES [Client] (
> [Id]
> )
> ) ON [PRIMARY]
> END
> GO
> INSERT INTO Client (Id) VALUES (1)
> INSERT INTO Client (Id) VALUES (2)
> INSERT INTO Activity ([ClientId], [UserIdEnteredBy], [DateofActivity])
> VALUES (1, 1, '01/01/2005')
> INSERT INTO Activity ([ClientId], [UserIdEnteredBy], [DateofActivity])
> VALUES (1, 2, '02/01/2005')
> INSERT INTO Activity ([ClientId], [UserIdEnteredBy], [DateofActivity])
> VALUES (1, 2, '03/01/2005')
> INSERT INTO Activity ([ClientId], [UserIdEnteredBy], [DateofActivity])
> VALUES (1, 1, '04/01/2005')
> I want a list of all Clients, and the LastContacted Date, and who the
> user who LastContacted them was.
> Here's what I have so far:
> SELECT
> C.Id,
> A.[UserIdEnteredBy] AS LastContactedBy,
> A.[DateofActivity] AS LastContacted
> FROM
> Client C
> LEFT OUTER JOIN Activity A ON C.Id = A.[ClientId]
> JOIN (SELECT ClientId, Max(Id) AS MaxActivityId From Activity GROUP BY
> ClientId) L
> ON A.Id = L.MaxActivityId AND A.ClientId = L.ClientId
> This gives me:
> Id LastContactedBy LastContacted
> ---
> 1 1 2005-04-01 00:00:00.000
> However, I want to return:
> Id LastContactedBy LastContacted
> ---
> 1 1 2005-04-01 00:00:00.000
> 2 NULL NULL
> This has got to be something with my joins, but I can't figure it out.
> Thanks!
>|||Thank you Barry, I'll check this out.|||Why did the client's id change names from table to table? Why did you
avoid real keys in favor proprietary, non-relational record numbering?
Is this what you meant, if you had a proper data model and had followed
ISO-11179 namign conventions? .
CREATE TABLE Clients
(client_id INTEGER NOT NULL PRIMARY KEY);
CREATE TABLE ClientActivity
(client_id INTEGER NOT NULL
REFERENCES Clients (client_id)
ON UPDATE CASCADE,
contact_user_id INTEGER NOT NULL,
REFERENCES Users (user_id)
ON UPDATE CASCADE,
contact_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (client_id, contact_user_id, activity_date));
SELECT client_id, contact_user_id, activity_date
FROM ClientActivity AS A1
WHERE A1.contact_date
= (SELECT MAX(A2.contact_date)
FROM ClientActivity AS A
WHERE A1.client_id = A2.client_id);
I am assuming that a client has to have had at least one contact. If
nothing else, to sign them as a client! There is no need for the
outer join or the Clients table.