Seán McCabe Seán McCabe - 3 months ago 8
MySQL Question

MySQL select values from Multiple Tables dependent on latest value in one

I have the following three tables to look after support tickets in a small web application, but I need some help getting the data I need.

Table 1 (ticket):

user_ID site_ID support_ID timestamp priority title
12 25 3 2014-09-26 14:09:25 0 A Test Row
12 26 4 2014-09-27 09:41:18 0 A 2nd Test Row


Table 2 (ticket_reply):

reply_ID support_ID user_ID support_reply reply_timestamp
3 3 12 some really boring text 2014-09-26 14:09:25
4 3 25 some really boring reply 2014-09-26 15:35:18
5 4 12 some really boring text 2014-09-27 09:41:18


Table 3 (ticket_status):

ticket_status_ID support_ID status_ID status_timestamp
3 3 40 2014-09-26 14:09:25
4 3 41 2014-09-26 15:35:18
5 4 40 2014-09-27 09:41:18


The 1st table holds the key ticket information, the 2nd, any replies made to the corresponding ticket, and the third tracks the change in status (statuses are held in another table, but don't need anything from there).

What I need to do is get the number of tickets where the latest status is == 40, and if this is greater than 0, get the latest reply along with the data from the first table.

I've tried multiple ways of doing this, but I am stuck. Don't really want to paste them here as they will likely confuse people, and I doubt they are even close.

Answer

This one was rather tricky, however here is a working solution for you.

This query will get the most recent support_reply value for all tickets where the most recent status_ID is 40.

SELECT
    ticket_status_ID,
    support_ID,
  status_ID,
  status_timestamp,
  reply_ID,
  support_reply,
  reply_timestamp,
  `timestamp` ticket_timestamp,
  `priority` ticket_priority,
  title
FROM (
    SELECT * FROM (
        SELECT * FROM (
        SELECT
            ticket_status.ticket_status_ID,
            ticket_status.support_ID,
            ticket_status.status_ID,
            ticket_status.status_timestamp,
            ts1.reply_ID,
            ts1.user_ID,
            ts1.support_reply,
            ts1.reply_timestamp
            FROM
            ticket_status
            INNER JOIN (SELECT * FROM ticket_reply ORDER BY reply_timestamp DESC) ts1 ON ts1.support_ID = ticket_status.support_ID
            GROUP BY support_ID, status_ID
            ORDER BY status_timestamp DESC
        ) ts2
        GROUP BY ts2.support_ID
    ) ts3
    INNER JOIN (SELECT support_ID as `ticket_support_ID`, site_ID, `timestamp`, priority, title FROM ticket) ts4 ON ts4.ticket_support_ID = ts3.support_ID
    WHERE ts3.status_ID = 40
) ts5
Comments