CODEWITHSUNDEEP
mysqlsqljointimestamp
Dru Freeman
Dru Freeman

Reputation: 1826

SQL join several tables based on latest entry in transaction table per join record

I have a transaction table with timestamps

The output will be a sort of the evt list
Output line count should equal db.evt record count.

The first column of each table is the Autoinc unique index.
In transaction, these are fks to the other tables.

The problem is that I need the transaction with the latest timestamp for the evt in the transaction table.

I am still relatively new to SQL (Using MySQL) and while I muddle through joins. I have no idea how to get the latest record by evID by timestamp.

I have looked at other questions on the topic but not found one that addresses mine. (Granted there are 14K on Joins alone, so I may have missed one)

Sample Table Data below: Table structure is hopefully obvious by I will edit it in if requested.

Edit: I've changed the names of tables and columns for clarity (and to avoid matching keywords)

I tried Stuart's answer below and got an error:

Answer:

SELECT
  eventTable.EvtName AS EvtD,
  transTable.TranAct AS LastTrans,
  userTable.UserName AS UsrNm
FROM
  transTables,
  INNER JOIN (
    SELECT evtID, MAX(TransID) TransID FROM transTable GROUP BY evtID 
  ) last ON last.evtID = transTable.evtID AND last.TransID = transTable.TransID
  INNER JOIN eventTable ON eventTable.evtID = transTable.evtID
  INNER JOIN userTable ON userTable.usId = transTable.usId

Response:

#1064 - You have an error in your SQL syntax; 
    check the manual that corresponds to your MySQL server version for the right syntax to use near
     'INNER JOIN (
        SELECT evtID, MAX(TransID) TransID FROM transTable GROUP BY evt' 
    at line 7

Tables:

db.transTable

| TransID | EvtID | TranAct   | timestamp               | UserID
----------------------------------------------------------------
|  1      |  1    |  add      | 2014-05-08 08:10:00.000 | 3
|  2      |  2    |  add      | 2014-05-08 09:10:00.000 | 2
|  3      |  3    |  add      | 2014-05-08 10:10:00.000 | 3
|  4      |  2    |  validate | 2014-05-08 11:10:00.000 | 5
|  5      |  3    |  validate | 2014-05-08 12:10:00.000 | 3
|  6      |  2    |  reverse  | 2014-05-08 13:10:00.000 | 1
|  7      |  1    |  edit     | 2014-05-08 14:10:00.000 | 4
|  8      |  4    |  add      | 2014-05-08 15:10:00.000 | 3
|  9      |  5    |  add      | 2014-05-08 16:10:00.000 | 2


db.eventTable

| EvtID | EvtName 
-----------------
|  1    |  Evt1
|  2    |  Evt2
|  3    |  Evt3
|  4    |  Evt4
|  5    |  Evt5


db.userTable

| UserID | UserName 
--------------------
|  1     |  Usr1      
|  2     |  Usr2      
|  3     |  Usr3      
|  4     |  Usr4      
|  5     |  Usr5

Desired output:

eventTable.EvtName AS EvtD
transTable.TranAct AS LastTrans
userTable.UserName AS UsrNm

| EvtD | LastTrans | UsrNm
--------------------------
| Evt1 | edit      | Usr4
| Evt2 | reverse   | Usr1
| Evt3 | validate  | Usr3
| Evt4 | add       | Usr3
| Evt5 | add       | Usr2

Much thanks for any assistance.

Upvotes: 0

Views: 1466

Answers (1)

Stuart Ingram
Stuart Ingram

Reputation: 101

Something like this shuold work where a derived table is used to eliminate all transactions except the latest per evId.

SELECT
  eventTable.EvtName AS EvtD,
  transTable.TranAct AS LastTrans,
  userTable.UserName AS UsrNm,
FROM
  transTable
  INNER JOIN (
    SELECT evId, MAX(UID) uid FROM transTable GROUP BY evId 
  ) last ON last.evId = transTable.evId AND last.uid = transTable.uid
  INNER JOIN eventTable ON eventTable.evId = transTable.evId
  INNER JOIN userTable ON userTable.usId = transTable.usId

Upvotes: 1

Related Questions