Multiple MySQL rows with the same identifier satisfying two different conditions simultaneously

Question

I use OTRS helpdesk ticket management system. When I execute the 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 request so that I can only list the lines of each ticket that has "% RAW %% new%" in the name, and at the same time the same ticket_id has a line that has "% Close" in it.

In other words, three lines of ticket_id 3, two lines of ticket_id 5 and two lines 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

I have no way to verify this right now, but I think this might work:

 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`.`id` IN (SELECT `ticket`.`id` FROM `ticket_history` WHERE `ticket_history`.`name` LIKE '%Raw% %new%') ORDER BY `ticket_history`.`ticket_id` , `ticket_history`.`id` ASC 

Answer 3

You cannot use a simple OR to check what you don't want in a result set. My first guess would be a correlated subquery. Maybe not very good, but it should work. Maybe something like this (from my head and completely 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) 

Answer 4

These are not duplicate lines. You have requested ticket history records that meet the conditions, as well as their tickets. The first identifier is the ticket identifier. The second identifier is the ticket history identifier. Since there are several ticket history entries for each ticket, you get several entries. I could try (but not tested):

 SELECT `ticket`.`id` FROM `ticket` INNER JOIN `ticket_history` t1 ON t1.`ticket_id` = `ticket`.`id` AND t1.`name` LIKE '%Raw% %new%' INNER JOIN `ticket_history` t2 ON t2.ticket_id = ticket.id AND t2.`name` LIKE '%Close' ORDER BY ticket.id ASC 

I removed the ticket_history fields from select because there is no way to get them without multiple entries, since you are essentially requesting ticket entries that correspond to two ticket history entries.

Answer 5

Finally, this is the right solution, which I built from your suggestions and help from several other Internet links. Thank you all for your help.

 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;