p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

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)));


  Return to Index