Essaye ceci :
SELECT NOTE, NOM_ET, PRENOM_ET
FROM inscrits_en
INNER JOIN etudiants ON inscrits_en.MATRICULE_ET=etudiants.MATRICULE_ET
WHERE inscrits_en.MNEMONIC
IN (
SELECT MNEMONIC
FROM cours
WHERE NOM_ANNEE = "BA3"
AND TYPE_DE_COURS = "TH"
AND CATEGORIE = "NORMAL"
)
ORDER BY NOM_ET