How to get data by mysql

Question

I have list player as below.

+-----------+------------+------------+
| player_id | mission_id | reg_time   |
+-----------+------------+------------+
|         1 |          1 | 1508392600 |
|         1 |          2 | 1508392800 |
|         1 |          3 | 1508392900 |
|         1 |          4 | 1508392822 |
|         2 |          1 | 1508392500 |
|         2 |          2 | 1508392600 |
|         2 |          3 | 1508392900 |
|         3 |          1 | 1508392600 |
|         3 |          2 | 1508392600 |
|         3 |          3 | 1508392900 |
|         3 |          4 | 1508392900 |
|         4 |          1 | 1508392600 |
|         4 |          3 | 1508392900 |
+-----------+------------+------------+

I want to get all of player_id has

mission_id = 1, 2 and reg_time <= 1508392800 and

mission_id = 3, 4 and reg_time > 1508392800 as below

+-----------+------------+------------+
| player_id | mission_id | reg_time   |
+-----------+------------+------------+
|         1 |          1 | 1508392600 |
|         1 |          2 | 1508392800 |
|         1 |          3 | 1508392900 |
|         1 |          4 | 1508392822 |
|         3 |          1 | 1508392600 |
|         3 |          2 | 1508392600 |
|         3 |          3 | 1508392900 |
|         3 |          4 | 1508392900 |
+-----------+------------+------------+

How i can do by mysql?

Answer 1

You could try this code to get your desired results.

SELECT * 
from `players` AS p 
where (exists (select * 
             from `players` as p1 
             where p1.player_id = p.player_id 
                   AND p1.mission_id = 1 AND p1.reg_time <= 1508392800 ) 
     AND exists (select * 
             from `players` as p2 
             where p2.player_id = p.player_id 
                   AND p2.mission_id = 2 AND p2.reg_time <= 1508392800)) 
AND (exists (select * 
             from `players` as p3 
             where p3.player_id = p.player_id 
                   AND p3.mission_id = 3 AND p3.reg_time > 1508392800) 
     AND exists (select * 
             from `players` as p4 
             where p4.player_id = p.player_id 
                   AND p4.mission_id = 4 AND p4.reg_time > 1508392800))