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 : 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;
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 "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;