How to select 1:n related records which are minimum 2 per type in MySQL

Question

I have the following database tables.

my_left_table 
left_id      name 
1            A
2            B
3            C

my_right_tabe
right_id    thing     left_id_fk    status
1           D          1            new
2           E          1            new
3           F          2            old 
4           G          3            old
5           H          3            new
6           I          3            new
7           J          1            old
8           K          2            old
9           L          2            new
10          M          3            old
11          N          3            old
12          O          1            new

My desired result is as follow.

my_left_table 
left_id     name 
3            C

How do I select the left records which its right records have AT LEAST 2 status is new AND 2 status is old. For example, left_id 1 is not the target because three of its right records have the status new but only one record has the status old.

So far I have is.

SELECT *, COUNT(my_right_tabe.left_id_fk) AS count_left_id_fk  
FROM my_left_table 
INNER JOIN my_right_tabe 
ON my_left_table.id = my_right_tabe.left_id_fk 
GROUP BY my_right_tabe.left_id_fk

Answer 1

Use the HAVING clause in MySQL Like the following

SELECT my_left_table.left_id, my_left_table.name
FROM my_left_table 
INNER JOIN my_right_tabe 
ON my_left_table.left_id = my_right_tabe.left_id_fk 
GROUP BY my_right_tabe.left_id_fk
HAVING SUM(my_right_tabe.status="new") >= 2 AND
       SUM(my_right_tabe.status="old") >= 2