MySQL multiple "left join" and "having"

Question

I have tables:

1.Workers(id,name)

2.BonusPenalties(id,worker_id,type,worker_id,value)

3.Fouls(id,worker_id,value)

for BonusPenalties column "type" can be "0" or "1" where 0 - is the bonus, 1 - is the penalty

so I need to be able to filter workers by the number of bonuses / penalties / fouls

Something like where count(BonusPenalties.id) > 5(for penalties) and count(BonusPenalties.id) >7(for bonuses) and count(Fouls.id) < 100

I tried to join this tables by left join and using "HAVING" but results goes wrong

Answer 1

write the example for joining if I will combine tables 2 and 3

CREATE TABLE effects (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    worker_id INT NOT NULL,
    FOREIGN KEY fk_worker_id REFERENCES worker (worker_id),
    type ENUM('bonus', 'penalty', 'foul') NOT NULL,
    value DECIMAL(10, 2) NOT NULL DEFAULT 0
);

Now there is no joining multiplying, and you may use something like

HAVING SUM(type='bonus') > 2
   AND SUM(type='foul') = 0

or

HAVING SUM(type='bonus') > 2 -- check bonuses amount
   AND SUM(CASE WHEN type='foul' THEN value ELSE 0 END) < 1000 -- check total sum of fouls

after GROUP BY.