Prova così

codice:
select p.nome_prodotto,c.nome_categoria,s.nome_sottocategoria
from prodotti p
left join categoria c 
on p.idcat = c.idcat
left join sottocategoria s
on p.idsotcat = s.idsotcat and c.idcat = s.idcat
order by c.idcat,s.idsotcat