MySQL multiple rows with same id meeting two different conditions at same time

Question

I am using OTRS helpdesk ticket management system. When i execute following Query:

SELECT  `ticket`.`id` ,  `ticket_history`.`ticket_id` ,  `ticket_history`.`id` ,
`ticket_history`.`name` ,  `ticket_history`.`create_time` 
FROM  `ticket_history` 
INNER JOIN  `ticket` ON  `ticket_history`.`ticket_id` =  `ticket`.`id` 
WHERE  `ticket_history`.`name` LIKE  '%Raw% %new%'
OR  `ticket_history`.`name` LIKE  '%Close'
ORDER BY  `ticket_history`.`ticket_id` ,  `ticket_history`.`id` ASC 

I get the following output:

+----+-----------+-----+-------------------------------------------+---------------------+
| id | ticket_id | id  |                   name                    |     create_time     |
+----+-----------+-----+-------------------------------------------+---------------------+
|  1 |         1 |  79 | %%Close                                   | 2013-06-10 11:50:33 |
|  2 |         2 |   2 | %%2013060810000011%%Raw%%3 normal%%new%%2 | 2013-06-08 21:59:02 |
|  3 |         3 |   5 | %%2013060810000021%%Raw%%3 normal%%new%%3 | 2013-06-08 21:59:03 |
|  3 |         3 |  22 | %%Close                                   | 2013-06-08 22:10:41 |
|  3 |         3 |  82 | %%Close                                   | 2013-06-10 11:50:49 |
|  4 |         4 |  88 | %%Close                                   | 2013-06-10 11:51:32 |
|  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 |
|  6 |         6 |  92 | %%2013061010000016%%Raw%%3 normal%%new%%6 | 2013-06-10 12:00:24 |
|  7 |         7 |  95 | %%2013061010000025%%Raw%%3 normal%%new%%7 | 2013-06-10 13:05:05 |
|  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 |
|  9 |         9 | 163 | %%2013061810000011%%Raw%%3 normal%%new%%9 | 2013-06-18 02:05:06 |
+----+-----------+-----+-------------------------------------------+---------------------+

I need to modify the above query so I can only list rows of each ticket_id which has "%RAW% %new%" in name and at the same time same ticket_id has a row which has "%Close" in it.

In other words, Three rows of ticket_id 3, Two rows of ticket_id 5 and Two Rows of ticket_id 8 should be displayed from the above output.

Answer 1

SELECT `ticket`.`id` ,  `ticket_history`.`ticket_id` ,  `ticket_history`.`id` ,`ticket_history`.`name` ,  `ticket_history`.`create_time`
FROM tickets t INNER JOIN ticket_history th
ON t.ticket_id = th.ticket_id
WHERE
EXISTS(SELECT 1 FROM ticket_history WHERE name LIKE '%Raw% %new%' AND ticket_id = t.ticket_id)
AND
EXISTS(SELECT 1 FROM ticket_history WHERE name LIKE '%Close' AND ticket_id = t.ticket_id)

Answer 2

You can't use a simple OR to check for something that you don't want in the result set. My first guess would be a correlated subquery. Might not perform especially well, but it should work. Maybe something like this (off the top of my head and totally untested):

SELECT ...
FROM ticket_history AS th INNER JOIN ticket ON ticket_history.ticket_id = ticket.id
WHERE ticket_history.name LIKE '%Raw% %new%' 
AND EXISTS (SELECT * FROM ticket_history WHERE name LIKE '%Close' AND ticket_id = th.ticket_id)