Problème LEFT JOIN après passage d'Access vers MySQL
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.
Code:
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; |
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:
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; |
