Bonjour,
j'ai besoin de créer une requête utilisant full outer join qui n'existe pas en mysql.
J'ai utiliser une union avec les jointure gauche et droite.

Ma requête :
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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
(select ifnull(tabMaj.mois, ifnull(tabBl.mois, tabMin.mois)) as mois, tabBl.nb_Bl, tabMaj.nb_Maj, tabMin.nb_Min
from
(select CONVERT(concat(month(items.time_stamp),'/',year(items.time_stamp) ) USING utf8 )  as mois, 
month(items.time_stamp) as nummois, count(distinct items.sequence_num) as nb_Bl from items
where items.space_id = 2
and items.cus_int_03 = 1
and year(items.time_stamp)= year(now())
and items.cus_int_02 in (1,3,4)
and items.severity = 1
group by month(items.time_stamp)) as tabBl left outer join
(select CONVERT(concat(month(items.time_stamp),'/',year(items.time_stamp) ) USING utf8 )  as mois, 
month(items.time_stamp) as nummois, count(distinct items.sequence_num) as nb_Maj from items
where items.space_id = 2
and items.cus_int_03 = 1
and year(items.time_stamp)= year(now())
and items.cus_int_02 in (1,3,4)
and items.severity = 2
group by month(items.time_stamp)) as tabMaj on tabBl.nummois = tabMaj.nummois
left outer join
(select CONVERT(concat(month(items.time_stamp),'/',year(items.time_stamp) ) USING utf8 )  as mois, 
month(items.time_stamp) as nummois, count(distinct items.sequence_num) as nb_Min from items
where items.space_id = 2
and items.cus_int_03 = 1
and year(items.time_stamp)= year(now())
and items.cus_int_02 in (1,3,4)
and items.severity = 3
group by month(items.time_stamp)) as tabMin on tabMin.nummois = tabMaj.nummois) 
union 
(select ifnull(tabMaj.mois, ifnull(tabBl.mois, tabMin.mois)) as mois, tabBl.nb_Bl, tabMaj.nb_Maj, tabMin.nb_Min
from
(select CONVERT(concat(month(items.time_stamp),'/',year(items.time_stamp) ) USING utf8 )  as mois, 
month(items.time_stamp) as nummois, count(distinct items.sequence_num) as nb_Bl from items
where items.space_id = 2
and items.cus_int_03 = 2
and year(items.time_stamp)= year(now())
and items.cus_int_02 in (1,3,4)
and items.severity = 1
group by month(items.time_stamp)) as tabBl right outer join
(select CONVERT(concat(month(items.time_stamp),'/',year(items.time_stamp) ) USING utf8 )  as mois, 
month(items.time_stamp) as nummois, count(distinct items.sequence_num) as nb_Maj from items
where items.space_id = 2
and items.cus_int_03 = 1
and year(items.time_stamp)= year(now())
and items.cus_int_02 in (1,3,4)
and items.severity = 2
and month(items.time_stamp) < month(STR_TO_DATE('30/04/2009', '%d/%m/%Y') ) + 1
group by month(items.time_stamp)) as tabMaj on tabBl.nummois = tabMaj.nummois
right outer join
(select CONVERT(concat(month(items.time_stamp),'/',year(items.time_stamp) ) USING utf8 )  as mois, 
month(items.time_stamp) as nummois, count(distinct items.sequence_num) as nb_Min from items
where items.space_id = 2
and items.cus_int_03 = 1
and year(items.time_stamp)= year(now())
and items.cus_int_02 in (1,3,4)
and items.severity = 3
group by month(items.time_stamp)) as tabMin on tabMin.nummois = tabMaj.nummois)
order by mois.
Le problème est que cette requête peut renvoyer deux résultats pour un même mois. J'aurais besoin de regrouper les résultats sur le champ mois et je ne peux pas le faire.

Est ce que quelqu'un aurait une idée de solution.