|
 |
activex_data_objects thread: SQL question
Message #1 by "Gary Clark" <glc.home@g...> on Tue, 17 Jul 2001 01:31:07
|
|
What would the appropriate SQL statement be for the following scenario:
I have a "Contacts" recordset, and a "Calls" recordset. The calls
recordset contains date/time, duration, and call result information that
relates back to "Contacts" by ContactId.
I want to select the Contacts.Name, Contacts.Phone, Calls.DateTime,
Calls.Duration, and Calls.Result for the very last call on a given Contact.
There mey be several calls for that contact, but how can I get just the
last one and have the query return only the one query record?
Message #2 by "Reed Mohn, Anders" <Anders.Reed.Mohn@i...> on Tue, 17 Jul 2001 11:15:50 +0200
|
|
Off the top of my head:
Select all calls for that contact in a subquery.
The main query then selects based on the
Max(Datetime) value from the subquery.
I'll see if I can put that down in a statement..
Cheers,
Anders :)
> -----Original Message-----
> From: Gary Clark [mailto:glc.home@g...]
> Sent: 17. juli 2001 03:31
> To: ActiveX_Data_Objects
> Subject: [activex_data_objects] SQL question
>
>
>
> What would the appropriate SQL statement be for the following
> scenario:
>
> I have a "Contacts" recordset, and a "Calls" recordset. The calls
> recordset contains date/time, duration, and call result
> information that
> relates back to "Contacts" by ContactId.
>
> I want to select the Contacts.Name, Contacts.Phone, Calls.DateTime,
> Calls.Duration, and Calls.Result for the very last call on a
> given Contact.
>
> There mey be several calls for that contact, but how can I
> get just the
> last one and have the query return only the one query record?
>
>
>
Message #3 by "Reed Mohn, Anders" <Anders.Reed.Mohn@i...> on Tue, 17 Jul 2001 11:27:46 +0200
|
|
This appears to work:
SELECT Con.ContactID, Con.Name, Con.Phone, Calls.Date, Calls.Duration,
Calls.Result
FROM Calls, Contacts AS Con
WHERE (((Calls.Date)=3D
(SELECT Max(Calls.Date) FROM Calls WHERE Calls.ContactID =3D
Con.ContactID)));
> -----Original Message-----
> From: Gary Clark [mailto:glc.home@g...]
> Sent: 17. juli 2001 03:31
> To: ActiveX_Data_Objects
> Subject: [activex_data_objects] SQL question
>
>
>
> What would the appropriate SQL statement be for the following
> scenario:
>
> I have a "Contacts" recordset, and a "Calls" recordset. The calls
> recordset contains date/time, duration, and call result
> information that
> relates back to "Contacts" by ContactId.
>
> I want to select the Contacts.Name, Contacts.Phone, Calls.DateTime,
> Calls.Duration, and Calls.Result for the very last call on a
> given Contact.
>
> There mey be several calls for that contact, but how can I
> get just the
> last one and have the query return only the one query record?
>
>
>
Message #4 by "Gary Clark" <glc.home@g...> on Tue, 17 Jul 2001 15:19:05
|
|
Thanks very much, I have that working now.
I now have one a bit more complex using the same tables:
I need to get the very last call for every Contact that has a call for a
given day.
> This appears to work:
>
>
> SELECT Con.ContactID, Con.Name, Con.Phone, Calls.Date, Calls.Duration,
> Calls.Result
> FROM Calls, Contacts AS Con
> WHERE (((Calls.Date)=3D
> (SELECT Max(Calls.Date) FROM Calls WHERE Calls.ContactID =3D
> Con.ContactID)));
>
Message #5 by "Reed Mohn, Anders" <Anders.Reed.Mohn@i...> on Tue, 17 Jul 2001 16:38:33 +0200
|
|
Oh, no you don't ....
This one you'll have to figure out yourself.
Think about this: what's the difference between
the query you have and the one you want to create?
If I understand your question correctly, then this is
a veeeery simple modification.
(Besides, we shouldn't be discussing this on an _ADO_ list, should we?)
If you can't figure it out, email me directly.
Cheers,
Anders :)
> > SELECT Con.ContactID, Con.Name, Con.Phone, Calls.Date,
> Calls.Duration,
> > Calls.Result
> > FROM Calls, Contacts AS Con
> > WHERE (((Calls.Date)=3D
> > (SELECT Max(Calls.Date) FROM Calls WHERE Calls.ContactID =3D
> > Con.ContactID)));
|
|
|
View Cart
