Is there any other way to extract the number of exam?

Hello everyone I have a problem that I need help with I’m trying to get number of exam for specified student

we have three tables :

etudient (N_Appoge,Nom,PRENOM,filiere,....)

module (NUM_Module,Nom_module,Nom_semestre)

Inscrit (N_Appoge,NUM_Module) // this table is the relation between etudiant & module 

so basically students with the same class means they have the same filiere and the same Nom_semestre

I tried this query :

set @num_exam=0;
SELECT DISTINCT N_APPOGE, NOM, PRENOM, filiere,Nom_semestre,
(@num_exam:=@num_exam+1) as num_exam FROM etudiant,
module 
WHERE 
  filiere in (SELECT filiere FROM etudiant WHERE N_APPOGE='1610065' ) 
  And 
  Nom_semestre In 
    (SELECT Nom_semestre from module WHERE Nom_semestre='s1' 
    and NUM_module in (SELECT NUM_module from inscrit where N_APPOGE='1610065')) 
ORDER by NOM;

but the result was not like I expected it shows two result of the same student with N_APPOGE='1610065' and multiple result for other student as well

It looks like DISTINCT in this case does not work what’s the solution here ?

Answer

You have a join between etudiant and module, but in the WHERE clause there is nothing which specifies how these two tables are related. As such, the database is performing a cross join (each row in the first table is joined to each row in the second table). Apparently you need to get the table inscrit involved:

SELECT DISTINCT e.N_APPOGE,
                e.NOM,
                e.PRENOM,
                e.filiere,
                m.Nom_semestre,
                (@num_exam:=@num_exam+1) as num_exam
  FROM etudiant e
  INNER JOIN inscrit i
    ON i.N_Appoge = e.N_Appoge
  INNER JOIN module m
    ON m.NUM_Module = i.NUM_Module
  WHERE e.filiere in (SELECT filiere
                       FROM etudiant
                       WHERE N_APPOGE='1610065') And 
        m.Nom_semestre In (SELECT Nom_semestre
                             from module
                             WHERE Nom_semestre='s1' and
                                   NUM_module in (SELECT NUM_module
                                                    from inscrit
                                                    where N_APPOGE='1610065')) 
  ORDER by NOM

Hopefully that will help.