MYSQL select count two table left join

Question

I have two table. I want count how many times a value is in other table. So the codice from table "sorgente" is different in table contatore, because I have the suffix 'BRANO-' before the code. I try with LIKE but it doesn't work.

sorgente
| codice | nome  |
|   15   | mario |
|   16   | mary  |

contatore
| nome_evento |   data     |
| BRANO-15    | 2020-08-15 |
| BRANO-15    | 2020-08-16 |
| BRANO-16    | 2020-08-14 |

So the query may be

SELECT sorgente.codice, count(contatore.nome_evento)
FROM sorgente
JOIN contatore
WHERE contatore.nome_evento LIKE '%-'sorgente.codice

but I have a wrong result

Answer 1

Use string concatenation. A subquery seems like a natural solution:

select
    s.codice,
    (
        select count(*) 
        from contatore c 
        where c.nome_evento = concat('BRANO-', s.codice) 
    ) no_matches_in_contatore
from sorgente s

But you can also join and aggregate:

select s.codice, count(c.nome_evento) no_matches_in_contatore
from sorgente s
left join contatore c on c.nome_evento = concat('BRANO-', s.codice) 
group by s.codice