Compare 2 MySQL Fields

I am trying to get a PHP script working based on the API our support system uses, but I am not proficient enough with MySQL to figure this out.

What I need to do is sort through and find all entries that have an "action" value of "New Support Ticket Opened", take the "tid" and find the corresponding entry that has an action of "New Ticket Response made by XXX" where XXX is anything except for "User". The problem I am running into is that there might actually be multiple entries that match that description, but I only want to find the first one.

The idea is that I want to display a graph that summarizes the average time it takes us to initially respond to a support ticket.

Here is the start of the SQL...

SELECT b1.tid AS Tid, MIN(DATEDIFF(b2.date,b1.date)) AS ResponseTime
FROM billing b1 inner join billing b2 
ON b1.tid = b2.tid
WHERE b1.action='New Support Ticket Opened'
AND b2.action LIKE 'N开发者_运维百科ew Ticket Response made by%'
GROUP BY Tid

Any help would be fantastic! Thank you.

You have to use a LIMIT clause:

SELECT b1.tid AS Tid, MIN(DATEDIFF(b2.date,b1.date)) AS ResponseTime
FROM billing b1 inner join billing b2 
ON b1.tid = b2.tid
WHERE b1.action='New Support Ticket Opened'
AND b2.action LIKE 'New Ticket Response made by%'
AND b2.action != 'New Ticket Response made by User'
GROUP BY Tid
LIMIT 1

SELECT b1.tid AS Tid, MIN(DATEDIFF(b2.date,b1.date)) AS ResponseTime
FROM billing b1 inner join billing b2 
ON b1.tid = b2.tid
WHERE b1.action='New Support Ticket Opened'
AND b2.action LIKE 'New Ticket Response made by%'
AND b2.action != 'New Ticket Response made by User' //to filter "New Ticket Response made by User"
GROUP BY Tid
LIMIT 1

take the "tid" and find the corresponding entry that has an action of "New Ticket Response made by XXX" where XXX is anything except for "User"

Simples:

SELECT b1.tid AS Tid, MIN(DATEDIFF(b2.date,b1.date)) AS ResponseTime
FROM billing b1 INNER join billing b2 
  ON b1.tid = b2.tid
WHERE b1.action='New Support Ticket Opened'
AND b2.action LIKE 'New Ticket Response made by%'
AND b2.action <> 'New Ticket Response made by User'
GROUP BY Tid

And if you want to find the actual user.....

SELECT SUBSTR(b2.action, LENGTH('New Ticket Response made by ')) AS user,
  b1.tid AS Tid, MIN(DATEDIFF(b2.date,b1.date)) AS ResponseTime
....

(since LENGTH('New Ticket Response made by ') is constant, you'll get better performance replacing it with a literal integer)