-



select suite_electronique.num_dossier FROM suite_electronique

where substr(suite_electronique.num_dossier,10) IN (select substr(dossier_ce.num_dossier,10) from dossier_ce where  dossier_ce.state_trt='r_valide')





`bac_international`,
`bac_dc`, 
`bac_di`, 
`bac_autre`,
`test_niv_atteint`,
`test_note`,
`test_francais`, 
`ville`, 
`res_pc`, 
`res_dut`, 
`res_dap_blanche`, 
`res_dap_jaune`, 
`res_m1`, 
`res_m2`, 
`res_l2`, 
`res_l3`, 
`res_rdv_consulaire_urgent`, 
`attente_orig_compt`, 
`en_attente_demarche`, 
`autre`

SELECT c1.nom as agent,c2.nom as entretien, 
suite_electronique.`num_dossier`, 
suite_electronique.`nom`, 
suite_electronique.`prenom`, 
suite_electronique.`sexe`, 
suite_electronique.`date_naissance`, 
suite_electronique.`email`, 
suite_electronique.`tel`, 
suite_electronique.`gsm`, 
suite_electronique.`type_dossier`, 
suite_electronique.`dernier_etabliss`, 
suite_electronique.`region_origine`, 
suite_electronique.`niv_etude_atteint`, 
suite_electronique.`res_pc`, 
suite_electronique.`res_dut`, 
suite_electronique.`res_dap_blanche`, 
suite_electronique.`res_dap_jaune`, 
suite_electronique.`res_m1`, 
suite_electronique.`res_m2`, 
suite_electronique.`res_l2`, 
suite_electronique.`res_l3`, 
suite_electronique.`res_rdv_consulaire_urgent`, 
suite_electronique.`attente_orig_compt`, 
suite_electronique.`en_attente_demarche`, 
suite_electronique.`autre`, 
suite_electronique.`bac_international`, 
suite_electronique.`bac_dc`, 
suite_electronique.`bac_di`, 
suite_electronique.`bac_autre`, 
suite_electronique.`test_niv_atteint`, 
suite_electronique.`test_note`, 
suite_electronique.`test_francais`, 
suite_electronique.`date_test`, 
suite_electronique.`ville`, 
suite_electronique.`etablissement`, 
suite_electronique.`filiere`, 
suite_electronique.`formation_universitaire`, 
suite_electronique.`formation_ingenieur`, 
suite_electronique.`ecole_prive`, 
suite_electronique.`date_rentree`, 
suite_electronique.`mineur`, 
suite_electronique.`parcours_grande_ecole`, 
suite_electronique.`date_p_soumission`, 
suite_electronique.`date_d_soumission`, 
suite_electronique.`nom_grp_etabliss_choisi` FROM `suite_electronique` left join compte as c1 on 
suite_electronique.fk_compte_agent=c1.id left join compte as c2 on 
suite_electronique.fk_compte_conseiller=c2.id



SELECT c1.nom as agent,c2.nom as entretien, 
dispense.`num_dossier`, 
dispense.`nom`, 
dispense.`prenom`, 
dispense.`sexe`, 
dispense.`date_naissance`, 
dispense.`email`, 
dispense.`tel`, 
dispense.`gsm`, 
dispense.`type_dossier`, 
dispense.`dernier_etabliss`, 
dispense.`region_origine`, 
dispense.`niv_etude_atteint`, 
dispense.`res_pc`, 
dispense.`res_dut`, 
dispense.`res_dap_blanche`, 
dispense.`res_dap_jaune`, 
dispense.`res_m1`, 
dispense.`res_m2`, 
dispense.`res_l2`, 
dispense.`res_l3`, 
dispense.`res_rdv_consulaire_urgent`, 
dispense.`attente_orig_compt`, 
dispense.`en_attente_demarche`, 
dispense.`autre`, 
dispense.`bac_international`, 
dispense.`bac_dc`, 
dispense.`bac_di`, 
dispense.`bac_autre`, 
dispense.`test_niv_atteint`, 
dispense.`test_note`, 
dispense.`test_francais`, 
dispense.`date_test`, 
dispense.`ville`, 
dispense.`etablissement`, 
dispense.`filiere`, 
dispense.`formation_universitaire`, 
dispense.`formation_ingenieur`, 
dispense.`ecole_prive`, 
dispense.`date_rentree`, 
dispense.`mineur`, 
dispense.`parcours_grande_ecole`, 
dispense.`date_p_soumission`, 
dispense.`date_d_soumission`, 
dispense.`nom_grp_etabliss_choisi` FROM `dispense` left join compte as c1 on 
dispense.fk_compte_agent=c1.id left join compte as c2 on 
dispense.fk_compte_conseiller=c2.id