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.

Help with a Join - join only first or max?

Hopefully someone will have some ideas on how to do this. I'm
officially stumped.

I have two entities to join. Simplified descriptions follow:

The first has names and addresses (vwPersonAddress) keyed by PersonID
(it is actually a view on two tables, but it works exactly as I want
it to, so all good there).

vwPersonAddress
-------
personID (PK)
addrType (PK)
fname
lname
addr1
city
st

entity 2 is a table that lists licenses and companies and is 1 to many
with vwPersonAddress (a person can have multiple licenses).

vwLicCo
---
personID (PK)
licenseID (PK)
licNum
CompanyName (can be null)

Now the odd part, I want to join them - but only get the first or max
company name from entity 2 for a given person. In other words, the
customer doesn't care WHAT company name I put in the output, as long
as it's only 1 (doesn't create extra records) and belongs to that
person. OH - and to keep it interesting, not every person will have a
company name in that second table...

I've played around quite a bit with all the join types and not found a
way to say, "Join these two tables, outer join on table 2 but if there
IS a match, only give me one"... that 'give me only one' bit is why I
was looking at max() by the way.

What I'm doing right now is running my output query on
vwPersonAddress, then as I create a data file (programmatically) doing
another query FOR EACH ROW on vwLicCo and just grabbing the first
companyName, if any. As you might guess, performance is less than
stellar. ;-)

Any thoughts?On Fri, 10 Dec 2004 12:24:16 -0700, csk wrote:

>Hopefully someone will have some ideas on how to do this. I'm
>officially stumped.
>I have two entities to join. Simplified descriptions follow:

Hi csk,

In the future, please post actual CREATE TABLE scripts, including all
constraints. And add in some sample data (as INSERT statements) plus
expected output, to clarify what you mean and to enable easy testing.

See www.aspfaq.com/5006

>The first has names and addresses (vwPersonAddress) keyed by PersonID
>(it is actually a view on two tables, but it works exactly as I want
>it to, so all good there).
>vwPersonAddress
>-------
>personID (PK)
>addrType (PK)
>fname
>lname
>addr1
>city
>st

I'm confused. Is personID the PK (as you state in the narrative) or is
there a compound key of personID + addrType (as suggested in the list
above)? This is where a CREATE TABLE statement with constraints would have
been better!

>entity 2 is a table that lists licenses and companies and is 1 to many
>with vwPersonAddress (a person can have multiple licenses).
>vwLicCo
>---
>personID (PK)
>licenseID (PK)
>licNum
>CompanyName (can be null)

If vwPersonAddress has personID/addrType as compound key, then this table
needs addrType as well to make a 1 to many relationship.

I guess that actually, both vwLicCo vwPersonAddress are related one to
many with a third table, Persons. They are not directly related. (A
licence belongs to a person, not to a person's home address or work
address).

>Now the odd part, I want to join them - but only get the first or max
>company name from entity 2 for a given person. In other words, the
>customer doesn't care WHAT company name I put in the output, as long
>as it's only 1 (doesn't create extra records) and belongs to that
>person. OH - and to keep it interesting, not every person will have a
>company name in that second table...

Do you still want to list the person (with company name NULL) or should
the person be completely omitted? This is where sample data and expected
output would have been better!

>I've played around quite a bit with all the join types and not found a
>way to say, "Join these two tables, outer join on table 2 but if there
>IS a match, only give me one"... that 'give me only one' bit is why I
>was looking at max() by the way.

I'll have to start making assumptions and wild guesses and I can't run any
tests, but I'll give it a shot.

If you want only the companyname, try

SELECT A.fname, A.lname, MAX(L.CompanyName)
FROM vwPersonAddress AS A
INNER JOIN vwLicCo AS L
ON L.personID = A.personID
GROUP BY A.personID, A.fname, A.lname

If you need the licencenumber as well:

SELECT A.fname, A.lname, L.CompanyName, L.licNum
FROM vwPersonAddress AS A
INNER JOIN vwLicCo AS L
ON L.personID = A.personID
WHERE NOT EXISTS
(SELECT *
FROM vwLicCo AS L2
WHERE L2.personID = L.personID
AND L2.CompanyName > L.CompanyName)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 10 Dec 2004 20:44:49 +0100, Hugo Kornelis
<hugo@.pe_NO_rFact.in_SPAM_fo> wrote:

>On Fri, 10 Dec 2004 12:24:16 -0700, csk wrote:
>>Hopefully someone will have some ideas on how to do this. I'm
>>officially stumped.
>>
>>I have two entities to join. Simplified descriptions follow:
>Hi csk,
>In the future, please post actual CREATE TABLE scripts, including all
>constraints. And add in some sample data (as INSERT statements) plus
>expected output, to clarify what you mean and to enable easy testing.
>See www.aspfaq.com/5006

My apologies, I should've looked for the FAQ first.
I was attempting to distill a much more complex problem into something
a bit more simple.

In retrospect, my example wasn't close enough to what I want to do,
but you've given me an idea to mull over. If it doesn't work I'll be
back with more pertinent/useful info.

Thank you!|||>> Any thoughts? <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

You also need to read ISO-11179 so you will stop putting those silly
"vw-' prefixes on VIEW names. What does a Volkswagen have to do with
this table??

Why is a license id totally different from a mere license number?
Surely you know better than to use IDENTITY or some other nonrelational
exposed physical locator in a real table.

CREATE TABLE PersonnelLocations
(ssn CHAR(9) NOT NULL -- or other industry standard
REFERENCES Personnel (ssn)
ON UPDATE CASCADE
ON DELETE CASCADE,
san INTEGER NOT NULL, san = Standard Address Number
REFERENCES Addresses (san)
ON UPDATE CASCADE,
...
PRIMARY KEY (ssn, san) );

CREATE TABLE LicenseHolders
(ssn CHAR(9) NOT NULL,
license_nbr INTEGER NOT NULL,
PRIMARY KEY (ssn, license_nbr)
company_name VARCHAR (35) DEFAULT '{{not a company}}' NOT NULL
...);

>> I want to join them - but only get the first or max company name
from entity 2 for a given person. In other words, the customer doesn't
care WHAT company name I put in the output, as long as it's only 1
(doesn't create extra records [sic]) and belongs to that
person. OH - and to keep it interesting, not every person will have a
company name in that second table... <<

You did not tell us what you want as output. Names? License numbers?
Also, rows are not records -- huge differences. Maybe this?

SELECT P.last_name, P.first_name, H.license_nbr, MAX(H.company_name)
FROM Personnel AS P
LEFT OUTER JOIN
LicenseHolders AS H
ON P.ssn = H.ssn
GROUP BY P.last_name, P.first_name, H.license_nbr;