Bonjour,
je transfert une base de données d'Access 2010 vers MySQL 5.1.38.
Une requête ne donne pas les mêmes résultats sur les LEFT JOIN table_og et table_aff. J'obtiens 55 lignes en MySQL contre 537 en Access.
Les conditions en rouge semblent "bloquer" le LEFT JOIN mais je n'arrive pas à corriger cela.
Remarques :
1. '079' est en fait un paramètre (ce qui explique les concat) ;
2. les données sont les mêmes sur Access que sur MySQL.
La requête Access est identique à celle de MySQL (à la syntaxe près). J'ai enlevé les crochets et parenthèses inutiles pour plus de clarté :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42 SELECT DISTINCT table_emp.Employe_CodeEmploye, table_emp.Employe_Nom, table_emp.Employe_Prenom, table_emp.Employe_Statut, table_user.User_Login, aff_role_concatene.Roles, resultat_all.UNI_UF, resultat_all.UNI_UG, resultat_all.UNI_LIBELLE FROM (((((resultat_all RIGHT JOIN table_emp ON resultat_all.EMP_MATRICULE_COURT = table_emp.Employe_CodeEmploye) LEFT JOIN table_aff ON table_emp.Employe_IDOrbis = table_aff.Employe_IDOrbis) LEFT JOIN table_user ON table_emp.Employe_IDOrbis = table_user.Employe_IDOrbis) LEFT JOIN aff_role_concatene ON table_user.User_IDOrbis = aff_role_concatene.User_IDOrbis) LEFT JOIN table_og ON table_emp.Employe_IDOrbis = table_og.Employe_IDOrbis) LEFT JOIN table_gh ON table_emp.Employe_CodeEmploye = table_gh.EMP_MATRICULE_COURT WHERE ( ( table_emp.Employe_CodeEmploye Not Like concat('G', right('079', 2), 'I___') And table_emp.Employe_CodeEmploye Not Like concat('G', right('079', 2), 'A___') And table_emp.Employe_CodeEmploye Not Like 'ADMIN%' And table_emp.Employe_CodeEmploye Not Like '%TEST%' And table_emp.Employe_CodeEmploye <> '0' And table_emp.Employe_CodeEmploye Not Like 'ATI%' And table_emp.Employe_CodeEmploye <> 'QUADRAT' And table_emp.Employe_CodeEmploye <> 'AGFA' ) AND (table_emp.Employe_Fin Is Null Or table_emp.Employe_Fin > Now()) AND (table_user.User_Fin Is Null Or table_user.User_Fin > Now()) AND table_og.AffGroup_NomCourt Like concat('%', '079', '%') AND table_gh.EMP_MATRICULE_COURT Is Null ) OR ( ( table_emp.Employe_CodeEmploye Not Like concat('G', right('079', 2), 'I___') And table_emp.Employe_CodeEmploye Not Like concat('G', right('079', 2), 'A___') And table_emp.Employe_CodeEmploye Not Like 'ADMIN%' And table_emp.Employe_CodeEmploye Not Like '%TEST%' And table_emp.Employe_CodeEmploye <>'0' And table_emp.Employe_CodeEmploye Not Like 'ATI%' And table_emp.Employe_CodeEmploye <> 'QUADRAT' And table_emp.Employe_CodeEmploye <> 'AGFA' ) AND (table_emp.Employe_Fin Is Null Or table_emp.Employe_Fin > Now()) AND (table_user.User_Fin Is Null Or table_user.User_Fin > Now()) AND table_gh.EMP_MATRICULE_COURT Is Null AND table_aff.AffStruct_NomCourt Like concat('079', '%') AND (table_aff.AffStruct_Fin Is Null Or table_aff.AffStruct_Fin > Now()) ) ORDER BY table_emp.Employe_CodeEmploye;
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42 SELECT DISTINCT table_emp.Employe_CodeEmploye, table_emp.Employe_Nom, table_emp.Employe_Prenom, table_emp.Employe_Statut, table_user.User_Login, aff_role_concatene.Roles, resultat_all.UNI_UF, resultat_all.UNI_UG, resultat_all.UNI_LIBELLE FROM (((((resultat_all RIGHT JOIN table_emp ON resultat_all.EMP_MATRICULE_COURT = table_emp.Employe_CodeEmploye) LEFT JOIN table_aff ON table_emp.Employe_IDOrbis = table_aff.Employe_IDOrbis) LEFT JOIN table_user ON table_emp.Employe_IDOrbis = table_user.Employe_IDOrbis) LEFT JOIN aff_role_concatene ON table_user.User_IDOrbis = aff_role_concatene.User_IDOrbis) LEFT JOIN table_og ON table_emp.Employe_IDOrbis = table_og.Employe_IDOrbis) LEFT JOIN table_gh ON table_emp.Employe_CodeEmploye = table_gh.EMP_MATRICULE_COURT WHERE ( ( table_emp.Employe_CodeEmploye Not Like "G79I???" And table_emp.Employe_CodeEmploye Not Like "G79A???" And table_emp.Employe_CodeEmploye Not Like "ADMIN*" And table_emp.Employe_CodeEmploye Not Like "*TEST*" And table_emp.Employe_CodeEmploye<>"0" And table_emp.Employe_CodeEmploye Not Like "ATI*" And table_emp.Employe_CodeEmploye Not Like "QUADRAT" And table_emp.Employe_CodeEmploye Not Like "AGFA" ) AND (table_emp.Employe_Fin Is Null Or table_emp.Employe_Fin>Now()) AND (table_user.User_Fin Is Null Or table_user.User_Fin>Now()) AND (table_og.AffGroup_NomCourt Like "*079*") AND (table_gh.EMP_MATRICULE_COURT Is Null) ) OR ( ( table_emp.Employe_CodeEmploye Not Like "G79I???" And table_emp.Employe_CodeEmploye Not Like "G79A???" And table_emp.Employe_CodeEmploye Not Like "ADMIN*" And table_emp.Employe_CodeEmploye Not Like "*TEST*" And table_emp.Employe_CodeEmploye<>"0" And table_emp.Employe_CodeEmploye Not Like "ATI*" And table_emp.Employe_CodeEmploye Not Like "QUADRAT" And table_emp.Employe_CodeEmploye Not Like "AGFA" ) AND (table_emp.Employe_Fin Is Null Or table_emp.Employe_Fin>Now()) AND (table_user.User_Fin Is Null Or table_user.User_Fin>Now()) AND (table_gh.EMP_MATRICULE_COURT Is Null) AND (table_aff.AffStruct_NomCourt Like "079*") AND (table_aff.AffStruct_Fin Is Null Or table_aff.AffStruct_Fin>Now()) ) ORDER BY table_emp.Employe_CodeEmploye;
Partager