Rearranging the output of mysql query When I run the following Query:

SELECT ticket.id, th.ticket_id , th.id, th.name, th.create_time 
  FROM ticket_history th
  JOIN ticket ON  th.ticket_id =  ticket.id 
 WHERE (
        th.name LIKE  '%\%Raw\%% %\%new\%%'
        AND EXISTS (SELECT * FROM ticket_history th1 
         WHERE th1.ticket_id = ticket.id AND th1.name LIKE '%\%Close')
       )
    OR (
        th.name LIKE  '%\%Close'
        AND EXISTS (SELECT * FROM ticket_history th2 
         WHERE th2.ticket_id = ticket.id AND th2.name LIKE '%\%Raw\%% %\%new\%%')
       )
 ORDER BY th.ticket_id , th.id ASC;


I get the following output:

+----+-----------+-----+-------------------------------------------+---------------------+
| id | ticket_id | id  |                   name                    |     create_time     |
+----+-----------+-----+-------------------------------------------+---------------------+
|  3 |         3 |   5 | %%2013060810000021%%Raw%%3 normal%%new%%3 | 2013-06-08 21:59:03 |
|  3 |         3 |  82 | %%Close                                   | 2013-06-10 11:50:49 |
|  5 |         5 |  64 | %%2013060910000019%%Raw%%3 normal%%new%%5 | 2013-06-09 17:12:09 |
|  5 |         5 |  85 | %%Close                                   | 2013-06-10 11:51:10 |
|  8 |         8 |  98 | %%2013061110000014%%Raw%%3 normal%%new%%8 | 2013-06-11 19:05:06 |
|  8 |         8 | 109 | %%Close                                   | 2013-06-17 23:57:35 |
+----+-----------+-----+-------------------------------------------+---------------------+


In the above output the entry which contains "Raw" and "new" keyword show me when that particular ticket was open and the entry which contains "Close" keyword shows me when that particular ticket was closed. 

What changes do I need to make in order to get the output as below:

+----+-----------+----+----------------------+---------------------+
| id | ticket_id | id |      Open time       |     Close_time      |
+----+-----------+----+----------------------+---------------------+
|  3 |         3 |  5 | 2013-06-08 21:59:03  | 2013-06-10 11:50:49 |
|  5 |         5 | 64 | 2013-06-09 17:12:09  | 2013-06-10 11:51:10 |
|  8 |         8 | 98 | 2013-06-11 19:05:06  | 2013-06-17 23:57:35 |
+----+-----------+----+----------------------+---------------------+
You don't seem to need the ticket table. The output does contain the ticket.id column, but it is always same as ticket_history.ticket_id, which is also included in the output.

In view of the above, the following query reads only ticket_history. It uses conditional aggregation to build the output you are after:

SELECT
  ticket_id,
  MIN(id) AS id,
  MAX(CASE name WHEN '%%Close' THEN NULL ELSE create_time END) AS open_time,
  MAX(CASE name WHEN '%%Close' THEN create_time ELSE NULL END) AS close_time
FROM ticket_history
GROUP BY
  ticket_id
HAVING COUNT(name LIKE '%\%Close'            OR NULL) = 1
   AND COUNT(name LIKE '%\%Raw\%% %\%new\%%' OR NULL) = 1
;


The HAVING clause makes sure a ticket has both open and close rows. The OR NULL trick is explained in this Stack Overflow answer:


Why do I need “OR NULL” in MySQL when counting rows with a condition


The id column is derived as MIN(id) on the assumption that a Close row will always appear after the corresponding Open one, and therefore the latter's id will always be lower than the former's.

The open_time and close_time values are obtained using conditional aggregation. Depending on whether the name column matches the '%\%Close' pattern or not, the create_time column is aggregated as either close_time or open_time.

You can use this SQL Fiddle demo to try the solution.