MySQL - Combining question answers from multiple transactions

Question

I have the follow tables in a database that I am using to store the results of surveys:

http://img19.imageshack.us/img19/5016/strtranans.jpg

Each time I speak to someone at a store (read Shop) I create a new transaction, with a date time (tblTransaction.Created). Each transaction can many question answers associated with it.

My problem is that 2 transactions could both contain different answers to the same question and I am trying to create an SQL query/procedure to return only the latest question answers for a given store.

Here's an example:

I speak to someone at Some Shop Plc. and create a transaction on the 01/01/09 (transaction ID 1). In this transaction I ask QuestionIDs 1 and 2. Then on the 10/01/09 I speak to them again, creating a new transaction (transaction ID 2) and answer questions 2 and 3.

I want to be able to show the list of latest answers; Question 1 from the first transaction and questions 2 and 3 from the second transaction.

Any suggestions would be greatly appreciated.

Answer 1

SELECT
     S.StoreID,
     S.BranchName,
     A.QuestionID,
     T.Created,
     A.*
FROM
     tblStore S
INNER JOIN tblTransaction T ON
     T.StoreID = S.StoreID
INNER JOIN tblAnswer A ON
     A.TransactionID = T.TransactionID AND
     A.StoreID = S.StoreID
WHERE NOT EXISTS
          (
          SELECT
               T2.StoreID,
               A2.QuestionID,
               T2.TransactionID,
               T2.CreatedDate
          FROM
               tblTransaction T2
          INNER JOIN tblAnswer A2 ON
               A2.TransactionID = T2.TransactionID AND
               A2.StoreID = T2.StoreID
          WHERE
               T2.StoreID = T.StoreID AND
               A2.QuestionID = A.QuestionID AND
               T2.CreatedDate > T.CreatedDate
          )

You can also do this by LEFT OUTER JOINing the subquery on the same criteria as in the WHERE clause and looking for a NULL value in it or you can remove the TransactionID from the subquery use a MAX() on the CreatedDate and look for a match that way with an INNER JOIN.

Keep in mind that this may not act how you are expecting if you have multiple transactions for the same store with the same created date. You may need to add an additional criteria there based on your business rules around that situation.