Query between two tables

[sharpnova]

Two tables:

Purchases:
-PurchaseID <-- Primary Key
-PurchaserName
-ProductID

PurchaseActions: (this table has no primary key)
-PurchaseID
-PurchaseStage (can have several values like Ordering, Delivering, DeliveryConfirmed, Survey, Support, etc.)
-PurchaseActionDate (the datetimestamp of when the purchasestage was entered)

Pretty simple table structure.

Here's what I want:

I want to run a query that will return one record for each record in table Purchases. And it will also return, for that given PurchaseID, a PurchaseStage value by linking it with the PurchaseActions table. I simply want it to return the LAST (according to PurchaseActionDate) PurchaseStage that was entered that was either Survey or Support.

example:

Purchases:
1 John 700
2 Thomas 838
3 Dan 136

PurchaseActions
1 Ordered 3/4/10 3:00 PM
1 Delivered 3/8/10 9:38 AM
1 Delivery-Confirmed 3/8/10 9:42 AM
1 Survey 3/8/10 10:38 AM
2 Ordered 4/11/10 2:31 PM
2 Picked-Up 4/11/10 4:18 PM
2 Survey 4/12/10 5:39 PM
2 Support 4/16/10 6:11 AM
2 Support 4/23/10 6:28 AM
3 Ordered 5/1/11 8:19 AM
3 Delivered 5/3/11 7:37 PM
3 Delivery-Confirmed 5/3/11 5:01 PM
3 Support 5/4/11 11:38 AM

And the query I'm trying to build would return:
1 John 700 Survey
2 Thomas 838 Support
3 Dan 136 Support

It returned all the records in Purchases, each accompanied by the last PurchaseStage for that Purchase that was Survey or Support.

 

[Taruz]

Hi..

I prepared a query for you .. I hope that gives the desired result..:

select 
   PurchaseActions.PurchaseID, 
   PurchaserName, 
   ProductID, 
   PurchaseStage
from (
      (
          select 
              PurchaseActions.PurchaseID, 
              max(PurchaseActionDate) as last_date
          from PurchaseActions
         group by PurchaseActions.PurchaseID) as trz
inner join PurchaseActions on trz.last_date=PurchaseActions.PurchaseActionDate) 
inner join Purchases on PurchaseActions.PurchaseID=Purchases.PurchaseID