Discussion :

[isa21493]

Bonjour à tous,

Je fais une requete sur ma base de donnée et je récupère ceci en sorti:

area: ------------ current week:

nord -------------- 2
sud ----------------3
est ----------------4
ouest --------------5
centre -------------5

ce que je voudrais c'est que si un champ est à 0 et bien qu'il affiche 0

area: ------------ current week:

nord -------------- 2
sud ----------------0
est ----------------4
ouest --------------5
centre -------------0

mais ma requete me renvoie ça:

area: ------------ current week:

nord -------------- 2
est ----------------4
ouest --------------5

il me les enleve.

savez vous comment faire?

Je voudrais également ne récupérer que ça:

current week:

2
3
4
5
5

mais je n'y arrive pas.

quand j'enleve dans mon select le area il me fait une erreur car je fais un group by sur area, il apprécie pas.

il me demande alors si je veux continuer et quand je dis oui il me donne ceci:

count(table.champ):

2
3
4
5
5

il ne prend plus en compte mon nom de colonne.

Savez vous comment faire???

Merci de votre aide

[Xo]

Bonjour

Quel techno SVP : du Access ? Du Excel ? Autre ?

Si si c'est Access, ça doit être posé dans le forum Access ou Langage SQL, mais sûrement pas dans le forum VB6.

Et quel que soit le forum où tu posteras, merci de spécifier un minimum de renseignements si tu souhaites être aidé : Poser des questions sur des résultats de requêtes sans nous détailler un minimum la structude de tes données et les requêtes qui t'ont permis de parvenir à ces résultats ne donnera certainement aucun résultat ...

[isa21493]

vous avez raison, excusez moi.

je travaille dans MS Query (excel).
Je bosse sur les requetes SQL, ma base est sur easy php.

[Xo]

Bonjour à tous,

Je fais une requete sur ma base de donnée et je récupère ceci en sorti:

area: ------------ current week:

nord -------------- 2
sud ----------------3
est ----------------4
ouest --------------5
centre -------------5

Peut-on avoir la requête SVP ?

ce que je voudrais c'est que si un champ est à 0 et bien qu'il affiche 0

area: ------------ current week:

nord -------------- 2
sud ----------------0
est ----------------4
ouest --------------5
centre -------------0

savez vous comment faire?

J'imagine qu'il faut une jointure externe, mais sans requête, impossible d'en dire plus ...
cf un topo sur les jointures externes http://sql.developpez.com/sqlaz/jointures/#L2.3

Je voudrais également ne récupérer que ça:
...
mais je n'y arrive pas.

Idem, requête SVP.

[isa21493]

voici ma requete, j'ai enlevé ce qu'il y avait dans WHERE cart c'est trop long:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT table_custom_field_string_table_0.value AS 'Area', Count(table_custom_field_string_table_0.bug_id) AS 'Current Week'
 
FROM base.table_bug_table table_bug_table_0, base.table_custom_field_string_table table_custom_field_string_table_0
 
WHERE ……….
 
GROUP BY table_custom_field_string_table_0.value

[Xo]

voici ma requete, j'ai enlevé ce qu'il y avait dans WHERE cart c'est trop long:

C'est bien dommage, car c'est sur cette clause qu'il faut agir pour la jointure externe

[isa21493]

ok alors voici ma requete entière:

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 table_custom_field_string_table_0.value AS 'Area', Count(table_custom_field_string_table_0.bug_id) AS 'Current Week'
 
FROM base.table_bug_table table_bug_table_0, base.table_custom_field_string_table table_custom_field_string_table_0
 
WHERE table_bug_table_0.id = table_custom_field_string_table_0.bug_id AND ((table_bug_table_0.status=80) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) 
 
AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) 
 
AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) 
 
AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) 
 
AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) 
 
AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) 
 
AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='support') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='support') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) 
 
AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='support') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='support') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) 
 
AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='support') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='support') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) 
 
AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='support') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='support') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) 
 
AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='support') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='support') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) 
 
AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='support') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='support') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) 
 
AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1)
 
 AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) 
 
AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) 
 
AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) 
 
AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) 
 
AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) 
 
AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=2) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=2) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) 
 
AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=2) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=2) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) 
 
AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=2) AND (table_bug_table_0.category='support') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=2) AND (table_bug_table_0.category='support') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) 
 
AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=2) AND (table_bug_table_0.category='support') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=2) AND (table_bug_table_0.category='support') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) 
 
AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=2) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=2) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1)
 
 AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=2) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=2) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) 
 
AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())))
 
 
GROUP BY table_custom_field_string_table_0.value

[TheRussian]

Peut-être que comme ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
SELECT table_custom_field_string_table_0.value AS 'Area', Count(table_custom_field_string_table_0.bug_id) AS 'Current Week' 
 
FROM base.table_bug_table table_bug_table_0 
right join base.table_custom_field_string_table table_custom_field_string_table_0 on table_bug_table_0.id = table_custom_field_string_table_0.bug_id 
 
WHERE ((table_bug_table_0.status=80) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) 
 
...
 
GROUP BY table_custom_field_string_table_0.value

[Xo]

ok alors voici ma requete entière:

Ha oui, elle est longue ...


Bon, sérieusement, le souci se situe uniquement dans la clause de la jointure entre tes 2 tables, pas sur le reste :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
... 
WHERE table_bug_table_0.id = table_custom_field_string_table_0.bug_id (+)
AND ...
GROUP BY table_custom_field_string_table_0.value

As-tu été voir le lien que je t'ai donné vers la syntaxe des jointures externes ? J'ai rajouté un (+) dans ta clause de jointure ci-dessus, c'est l'ancienne syntaxe, et si tu écris tes jointures entre tables avec un JOIN, alors il faudra procéder avec un LEFT/RIGHT OUTER JOIN.

[isa21493]

j'ai fait ça mais ça ne marche pas:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT table_custom_field_string_table_0.value AS 'Area', Count(table_custom_field_string_table_0.bug_id) AS 'Current Week'
 
FROM base.table_bug_table table_bug_table_0 right join  base.table_custom_field_string_table table_custom_field_string_table_0 on table_bug_table_0.id = table_custom_field_string_table_0.bug_id
 
WHERE ((table_bug_table_0.status=80) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='support') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='support') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='support') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='support') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='support') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='support') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='support') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='support') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='support') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='support') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='support') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='support') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=2) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=2) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=2) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=2) AND (table_bug_table_0.category='bug') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=2) AND (table_bug_table_0.category='support') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=2) AND (table_bug_table_0.category='support') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=2) AND (table_bug_table_0.category='support') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=2) AND (table_bug_table_0.category='support') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=2) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) OR (table_bug_table_0.status=80) AND (table_bug_table_0.project_id=2) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=2) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())=1) AND (MONTH(table_bug_table_0.date_submitted)=12) AND (YEAR(table_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) OR (table_bug_table_0.status=90) AND (table_bug_table_0.project_id=2) AND (table_bug_table_0.category='correction') AND (MONTH(NOW())>1) AND (table_custom_field_string_table_0.field_id=1) AND (MONTH(table_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(table_bug_table_0.date_submitted)=YEAR(NOW())))
 
GROUP BY table_custom_field_string_table_0.value

[isa21493]

je viens d'essayer avec right outer join au lieu de right join

ça ne marche pas
j'ai une erreur avec l'un comme avec l'autre

[TheRussian]

En fait le problème vient de test sur les valeurs de la table "base.table_bug_table", en rajoutant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
SELECT ......
 
WHERE (...............)
 
OR table_bug_table.id is null
 
.......

cela devrait marcher.

[isa21493]

je comprend pas trop pk il faut que je mette ça, car c'est mon 'Count (table_custom_field_string_table_0.bug_id)' qui doit etre afficher meme s'il est égal à 0 et non le bug_id

[isa21493]

j'ai essayé d'imbriquer 2 select pour récupérer la 2ème colonne mais ça marche pas, qqun voit il mon erreur?
l'erreur me dit qu'il ne trouve pas la table (select ...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT Count(table_custom_field_string_table_0.bug_id) AS 'Current WEBSHOP' 
 
FROM (SELECT table_custom_field_string_table_0.value AS 'Area', Count(table_custom_field_string_table_0.bug_id)
FROM base.table_bug_table table_bug_table_0, base.table_custom_field_string_table table_custom_field_string_table_0
WHERE (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='bug') AND (table_bug_table_0.status<>80 And table_bug_table_0.status<>90) AND (week(table_bug_table_0.last_updated,7)=week(NOW(),7)) AND (YEAR(table_bug_table_0.last_updated)=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='bug') AND (table_bug_table_0.status<>80 And table_bug_table_0.status<>90) AND (week(table_bug_table_0.last_updated,7)=week(NOW(),7)) AND (YEAR(table_bug_table_0.last_updated)=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='bug') AND (table_bug_table_0.status<>80 And table_bug_table_0.status<>90) AND (week(table_bug_table_0.last_updated,7)=week(NOW(),7)) AND (YEAR(table_bug_table_0.last_updated)=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='support') AND (table_bug_table_0.status<>80 And table_bug_table_0.status<>90) AND (week(table_bug_table_0.last_updated,7)=week(NOW(),7)) AND (YEAR(table_bug_table_0.last_updated)=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='support') AND (table_bug_table_0.status<>80 And table_bug_table_0.status<>90) AND (week(table_bug_table_0.last_updated,7)=week(NOW(),7)) AND (YEAR(table_bug_table_0.last_updated)=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='support') AND (table_bug_table_0.status<>80 And table_bug_table_0.status<>90) AND (week(table_bug_table_0.last_updated,7)=week(NOW(),7)) AND (YEAR(table_bug_table_0.last_updated)=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.project_id=8) AND (table_bug_table_0.category='correction') AND (table_bug_table_0.status<>80 And table_bug_table_0.status<>90) AND (week(table_bug_table_0.last_updated,7)=week(NOW(),7)) AND (YEAR(table_bug_table_0.last_updated)=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.project_id=19) AND (table_bug_table_0.category='correction') AND (table_bug_table_0.status<>80 And table_bug_table_0.status<>90) AND (week(table_bug_table_0.last_updated,7)=week(NOW(),7)) AND (YEAR(table_bug_table_0.last_updated)=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id) OR (table_bug_table_0.project_id=20) AND (table_bug_table_0.category='correction') AND (table_bug_table_0.status<>80 And table_bug_table_0.status<>90) AND (week(table_bug_table_0.last_updated,7)=week(NOW(),7)) AND (YEAR(table_bug_table_0.last_updated)=YEAR(NOW())) AND (table_custom_field_string_table_0.field_id=1) AND (table_bug_table_0.id=table_custom_field_string_table_0.bug_id)
GROUP BY table_custom_field_string_table_0.value)

[isa21493]

j'ai mis la jointure mais aucun changement, je ne comprend pas

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
SELECT mantis_custom_field_string_table_0.value AS 'Area', Count(mantis_custom_field_string_table_0.bug_id) AS 'Current Week'
 
FROM {oj bugtracker.mantis_custom_field_string_table mantis_custom_field_string_table_0 LEFT OUTER JOIN bugtracker.mantis_bug_table mantis_bug_table_0 ON mantis_custom_field_string_table_0.bug_id = mantis_bug_table_0.id}
 
WHERE (mantis_bug_table_0.status=80) AND (mantis_bug_table_0.project_id=8) AND (mantis_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(mantis_bug_table_0.date_submitted)=12) AND (YEAR(mantis_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (mantis_custom_field_string_table_0.field_id=1) OR (mantis_bug_table_0.status=80) AND (mantis_bug_table_0.project_id=8) AND (mantis_bug_table_0.category='bug') AND (MONTH(NOW())>1) AND (mantis_custom_field_string_table_0.field_id=1) AND (MONTH(mantis_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(mantis_bug_table_0.date_submitted)=YEAR(NOW())) OR (mantis_bug_table_0.status=90) AND (mantis_bug_table_0.project_id=8) AND (mantis_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(mantis_bug_table_0.date_submitted)=12) AND (YEAR(mantis_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (mantis_custom_field_string_table_0.field_id=1) OR (mantis_bug_table_0.status=90) AND (mantis_bug_table_0.project_id=8) AND (mantis_bug_table_0.category='bug') AND (MONTH(NOW())>1) AND (mantis_custom_field_string_table_0.field_id=1) AND (MONTH(mantis_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(mantis_bug_table_0.date_submitted)=YEAR(NOW())) OR (mantis_bug_table_0.status=80) AND (mantis_bug_table_0.project_id=19) AND (mantis_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(mantis_bug_table_0.date_submitted)=12) AND (YEAR(mantis_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (mantis_custom_field_string_table_0.field_id=1) OR (mantis_bug_table_0.status=80) AND (mantis_bug_table_0.project_id=19) AND (mantis_bug_table_0.category='bug') AND (MONTH(NOW())>1) AND (mantis_custom_field_string_table_0.field_id=1) AND (MONTH(mantis_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(mantis_bug_table_0.date_submitted)=YEAR(NOW())) OR (mantis_bug_table_0.status=90) AND (mantis_bug_table_0.project_id=19) AND (mantis_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(mantis_bug_table_0.date_submitted)=12) AND (YEAR(mantis_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (mantis_custom_field_string_table_0.field_id=1) OR (mantis_bug_table_0.status=90) AND (mantis_bug_table_0.project_id=19) AND (mantis_bug_table_0.category='bug') AND (MONTH(NOW())>1) AND (mantis_custom_field_string_table_0.field_id=1) AND (MONTH(mantis_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(mantis_bug_table_0.date_submitted)=YEAR(NOW())) OR (mantis_bug_table_0.status=80) AND (mantis_bug_table_0.project_id=20) AND (mantis_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(mantis_bug_table_0.date_submitted)=12) AND (YEAR(mantis_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (mantis_custom_field_string_table_0.field_id=1) OR (mantis_bug_table_0.status=80) AND (mantis_bug_table_0.project_id=20) AND (mantis_bug_table_0.category='bug') AND (MONTH(NOW())>1) AND (mantis_custom_field_string_table_0.field_id=1) AND (MONTH(mantis_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(mantis_bug_table_0.date_submitted)=YEAR(NOW())) OR (mantis_bug_table_0.status=90) AND (mantis_bug_table_0.project_id=20) AND (mantis_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(mantis_bug_table_0.date_submitted)=12) AND (YEAR(mantis_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (mantis_custom_field_string_table_0.field_id=1) OR (mantis_bug_table_0.status=90) AND (mantis_bug_table_0.project_id=20) AND (mantis_bug_table_0.category='bug') AND (MONTH(NOW())>1) AND (mantis_custom_field_string_table_0.field_id=1) AND (MONTH(mantis_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(mantis_bug_table_0.date_submitted)=YEAR(NOW())) OR (mantis_bug_table_0.status=80) AND (mantis_bug_table_0.project_id=8) AND (mantis_bug_table_0.category='support') AND (MONTH(NOW())=1) AND (MONTH(mantis_bug_table_0.date_submitted)=12) AND (YEAR(mantis_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (mantis_custom_field_string_table_0.field_id=1) OR (mantis_bug_table_0.status=80) AND (mantis_bug_table_0.project_id=8) AND (mantis_bug_table_0.category='support') AND (MONTH(NOW())>1) AND (mantis_custom_field_string_table_0.field_id=1) AND (MONTH(mantis_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(mantis_bug_table_0.date_submitted)=YEAR(NOW())) OR (mantis_bug_table_0.status=90) AND (mantis_bug_table_0.project_id=8) AND (mantis_bug_table_0.category='support') AND (MONTH(NOW())=1) AND (MONTH(mantis_bug_table_0.date_submitted)=12) AND (YEAR(mantis_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (mantis_custom_field_string_table_0.field_id=1) OR (mantis_bug_table_0.status=90) AND (mantis_bug_table_0.project_id=8) AND (mantis_bug_table_0.category='support') AND (MONTH(NOW())>1) AND (mantis_custom_field_string_table_0.field_id=1) AND (MONTH(mantis_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(mantis_bug_table_0.date_submitted)=YEAR(NOW())) OR (mantis_bug_table_0.status=80) AND (mantis_bug_table_0.project_id=19) AND (mantis_bug_table_0.category='support') AND (MONTH(NOW())=1) AND (MONTH(mantis_bug_table_0.date_submitted)=12) AND (YEAR(mantis_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (mantis_custom_field_string_table_0.field_id=1) OR (mantis_bug_table_0.status=80) AND (mantis_bug_table_0.project_id=19) AND (mantis_bug_table_0.category='support') AND (MONTH(NOW())>1) AND (mantis_custom_field_string_table_0.field_id=1) AND (MONTH(mantis_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(mantis_bug_table_0.date_submitted)=YEAR(NOW())) OR (mantis_bug_table_0.status=90) AND (mantis_bug_table_0.project_id=19) AND (mantis_bug_table_0.category='support') AND (MONTH(NOW())=1) AND (MONTH(mantis_bug_table_0.date_submitted)=12) AND (YEAR(mantis_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (mantis_custom_field_string_table_0.field_id=1) OR (mantis_bug_table_0.status=90) AND (mantis_bug_table_0.project_id=19) AND (mantis_bug_table_0.category='support') AND (MONTH(NOW())>1) AND (mantis_custom_field_string_table_0.field_id=1) AND (MONTH(mantis_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(mantis_bug_table_0.date_submitted)=YEAR(NOW())) OR (mantis_bug_table_0.status=80) AND (mantis_bug_table_0.project_id=20) AND (mantis_bug_table_0.category='support') AND (MONTH(NOW())=1) AND (MONTH(mantis_bug_table_0.date_submitted)=12) AND (YEAR(mantis_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (mantis_custom_field_string_table_0.field_id=1) OR (mantis_bug_table_0.status=80) AND (mantis_bug_table_0.project_id=20) AND (mantis_bug_table_0.category='support') AND (MONTH(NOW())>1) AND (mantis_custom_field_string_table_0.field_id=1) AND (MONTH(mantis_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(mantis_bug_table_0.date_submitted)=YEAR(NOW())) OR (mantis_bug_table_0.status=90) AND (mantis_bug_table_0.project_id=20) AND (mantis_bug_table_0.category='support') AND (MONTH(NOW())=1) AND (MONTH(mantis_bug_table_0.date_submitted)=12) AND (YEAR(mantis_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (mantis_custom_field_string_table_0.field_id=1) OR (mantis_bug_table_0.status=90) AND (mantis_bug_table_0.project_id=20) AND (mantis_bug_table_0.category='support') AND (MONTH(NOW())>1) AND (mantis_custom_field_string_table_0.field_id=1) AND (MONTH(mantis_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(mantis_bug_table_0.date_submitted)=YEAR(NOW())) OR (mantis_bug_table_0.status=80) AND (mantis_bug_table_0.project_id=8) AND (mantis_bug_table_0.category='correction') AND (MONTH(NOW())=1) AND (MONTH(mantis_bug_table_0.date_submitted)=12) AND (YEAR(mantis_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (mantis_custom_field_string_table_0.field_id=1) OR (mantis_bug_table_0.status=80) AND (mantis_bug_table_0.project_id=8) AND (mantis_bug_table_0.category='correction') AND (MONTH(NOW())>1) AND (mantis_custom_field_string_table_0.field_id=1) AND (MONTH(mantis_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(mantis_bug_table_0.date_submitted)=YEAR(NOW())) OR (mantis_bug_table_0.status=90) AND (mantis_bug_table_0.project_id=8) AND (mantis_bug_table_0.category='correction') AND (MONTH(NOW())=1) AND (MONTH(mantis_bug_table_0.date_submitted)=12) AND (YEAR(mantis_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (mantis_custom_field_string_table_0.field_id=1) OR (mantis_bug_table_0.status=90) AND (mantis_bug_table_0.project_id=8) AND (mantis_bug_table_0.category='correction') AND (MONTH(NOW())>1) AND (mantis_custom_field_string_table_0.field_id=1) AND (MONTH(mantis_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(mantis_bug_table_0.date_submitted)=YEAR(NOW())) OR (mantis_bug_table_0.status=80) AND (mantis_bug_table_0.project_id=19) AND (mantis_bug_table_0.category='correction') AND (MONTH(NOW())=1) AND (MONTH(mantis_bug_table_0.date_submitted)=12) AND (YEAR(mantis_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (mantis_custom_field_string_table_0.field_id=1) OR (mantis_bug_table_0.status=80) AND (mantis_bug_table_0.project_id=19) AND (mantis_bug_table_0.category='correction') AND (MONTH(NOW())>1) AND (mantis_custom_field_string_table_0.field_id=1) AND (MONTH(mantis_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(mantis_bug_table_0.date_submitted)=YEAR(NOW())) OR (mantis_bug_table_0.status=90) AND (mantis_bug_table_0.project_id=19) AND (mantis_bug_table_0.category='correction') AND (MONTH(NOW())=1) AND (MONTH(mantis_bug_table_0.date_submitted)=12) AND (YEAR(mantis_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (mantis_custom_field_string_table_0.field_id=1) OR (mantis_bug_table_0.status=90) AND (mantis_bug_table_0.project_id=19) AND (mantis_bug_table_0.category='correction') AND (MONTH(NOW())>1) AND (mantis_custom_field_string_table_0.field_id=1) AND (MONTH(mantis_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(mantis_bug_table_0.date_submitted)=YEAR(NOW())) OR (mantis_bug_table_0.status=80) AND (mantis_bug_table_0.project_id=20) AND (mantis_bug_table_0.category='correction') AND (MONTH(NOW())=1) AND (MONTH(mantis_bug_table_0.date_submitted)=12) AND (YEAR(mantis_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (mantis_custom_field_string_table_0.field_id=1) OR (mantis_bug_table_0.status=80) AND (mantis_bug_table_0.project_id=20) AND (mantis_bug_table_0.category='correction') AND (MONTH(NOW())>1) AND (mantis_custom_field_string_table_0.field_id=1) AND (MONTH(mantis_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(mantis_bug_table_0.date_submitted)=YEAR(NOW())) OR (mantis_bug_table_0.status=90) AND (mantis_bug_table_0.project_id=20) AND (mantis_bug_table_0.category='correction') AND (MONTH(NOW())=1) AND (MONTH(mantis_bug_table_0.date_submitted)=12) AND (YEAR(mantis_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (mantis_custom_field_string_table_0.field_id=1) OR (mantis_bug_table_0.status=90) AND (mantis_bug_table_0.project_id=20) AND (mantis_bug_table_0.category='correction') AND (MONTH(NOW())>1) AND (mantis_custom_field_string_table_0.field_id=1) AND (MONTH(mantis_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(mantis_bug_table_0.date_submitted)=YEAR(NOW())) OR (mantis_bug_table_0.status=80) AND (mantis_bug_table_0.project_id=2) AND (mantis_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(mantis_bug_table_0.date_submitted)=12) AND (YEAR(mantis_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (mantis_custom_field_string_table_0.field_id=1) OR (mantis_bug_table_0.status=80) AND (mantis_bug_table_0.project_id=2) AND (mantis_bug_table_0.category='bug') AND (MONTH(NOW())>1) AND (mantis_custom_field_string_table_0.field_id=1) AND (MONTH(mantis_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(mantis_bug_table_0.date_submitted)=YEAR(NOW())) OR (mantis_bug_table_0.status=90) AND (mantis_bug_table_0.project_id=2) AND (mantis_bug_table_0.category='bug') AND (MONTH(NOW())=1) AND (MONTH(mantis_bug_table_0.date_submitted)=12) AND (YEAR(mantis_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (mantis_custom_field_string_table_0.field_id=1) OR (mantis_bug_table_0.status=90) AND (mantis_bug_table_0.project_id=2) AND (mantis_bug_table_0.category='bug') AND (MONTH(NOW())>1) AND (mantis_custom_field_string_table_0.field_id=1) AND (MONTH(mantis_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(mantis_bug_table_0.date_submitted)=YEAR(NOW())) OR (mantis_bug_table_0.status=80) AND (mantis_bug_table_0.project_id=2) AND (mantis_bug_table_0.category='support') AND (MONTH(NOW())=1) AND (MONTH(mantis_bug_table_0.date_submitted)=12) AND (YEAR(mantis_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (mantis_custom_field_string_table_0.field_id=1) OR (mantis_bug_table_0.status=80) AND (mantis_bug_table_0.project_id=2) AND (mantis_bug_table_0.category='support') AND (MONTH(NOW())>1) AND (mantis_custom_field_string_table_0.field_id=1) AND (MONTH(mantis_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(mantis_bug_table_0.date_submitted)=YEAR(NOW())) OR (mantis_bug_table_0.status=90) AND (mantis_bug_table_0.project_id=2) AND (mantis_bug_table_0.category='support') AND (MONTH(NOW())=1) AND (MONTH(mantis_bug_table_0.date_submitted)=12) AND (YEAR(mantis_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (mantis_custom_field_string_table_0.field_id=1) OR (mantis_bug_table_0.status=90) AND (mantis_bug_table_0.project_id=2) AND (mantis_bug_table_0.category='support') AND (MONTH(NOW())>1) AND (mantis_custom_field_string_table_0.field_id=1) AND (MONTH(mantis_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(mantis_bug_table_0.date_submitted)=YEAR(NOW())) OR (mantis_bug_table_0.status=80) AND (mantis_bug_table_0.project_id=2) AND (mantis_bug_table_0.category='correction') AND (MONTH(NOW())=1) AND (MONTH(mantis_bug_table_0.date_submitted)=12) AND (YEAR(mantis_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (mantis_custom_field_string_table_0.field_id=1) OR (mantis_bug_table_0.status=80) AND (mantis_bug_table_0.project_id=2) AND (mantis_bug_table_0.category='correction') AND (MONTH(NOW())>1) AND (mantis_custom_field_string_table_0.field_id=1) AND (MONTH(mantis_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(mantis_bug_table_0.date_submitted)=YEAR(NOW())) OR (mantis_bug_table_0.status=90) AND (mantis_bug_table_0.project_id=2) AND (mantis_bug_table_0.category='correction') AND (MONTH(NOW())=1) AND (MONTH(mantis_bug_table_0.date_submitted)=12) AND (YEAR(mantis_bug_table_0.date_submitted)+1=YEAR(NOW())) AND (mantis_custom_field_string_table_0.field_id=1) OR (mantis_bug_table_0.status=90) AND (mantis_bug_table_0.project_id=2) AND (mantis_bug_table_0.category='correction') AND (MONTH(NOW())>1) AND (mantis_custom_field_string_table_0.field_id=1) AND (MONTH(mantis_bug_table_0.date_submitted)+1=MONTH(NOW())) AND (YEAR(mantis_bug_table_0.date_submitted)=YEAR(NOW()))
 
GROUP BY mantis_custom_field_string_table_0.value

[Xo]

je viens d'essayer avec right outer join au lieu de right join

ça ne marche pas
j'ai une erreur avec l'un comme avec l'autre

Non, à mon avis, c'était un LEFT OUTER JOIN, que tu aurais du mettre à ce moment : ré-essaye et dis-nous si c'est OK.

j'ai mis la jointure mais aucun changement, je ne comprend pas

Ca ne m'étonne pas vraiment, si tu t'entêtes à essayer de modifier cette requête monstrueuse directement, on y arrivera jamais ...


As-tu compris le principe de la jointure externe ? Sais-tu comment l'implémenter sous MySQL / MS Query ???

Voici un exemple simple, bricolé sous Oracle pour t'y aider si besoin :

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
-- création d'une table Parent :
CREATE TABLE Parent (
idParent NUMBER(10),
LbParent VARCHAR2(32));
 
-- création d'une table Fils :
CREATE TABLE Fils (
idFils NUMBER(10),
idParent NUMBER(10),
LbFils VARCHAR2(32));
 
--Insertion de 2 enregistrements dans Parent :
INSERT INTO Parent VALUES (1, 'Toto');
INSERT INTO Parent VALUES (2, 'Tutu');
 
--Insertion de 2 Fils, sur le premier Parent :
INSERT INTO Fils VALUES (1, 1, 'premier fils de Toto');
INSERT INTO Fils VALUES (2, 1, 'second fils de Toto');
-- ... donc le second parent a 0 Fils !

Une requête pour compter le nombre de fils classique me ramène uniquement les parents sans fils :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SQL> SELECT P.idParent, COUNT(idFils)
  2    FROM Parent P INNER JOIN Fils F ON P.idParent = F.idParent
  3   GROUP BY P.idParent;
 
  IDPARENT COUNT(IDFILS)
---------- -------------
         1             2

Mais si tu implémentes une jointure externe, tu auras bien ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SQL> SELECT P.idParent, COUNT(idFils)
  2    FROM Parent P LEFT OUTER JOIN Fils F ON P.idParent = F.idParent
  3   GROUP BY P.idParent;
 
  IDPARENT COUNT(IDFILS)
---------- -------------
         1             2
         2             0

[isa21493]

bonjour à tous,

je sais pourquoi ma jointure ne marche pas

en fait voici un exemple de ce que je fais et de ce que je veux:

j'ai une table "bugs":

name: ------------ id

bug 1 -------------- 1
bug 1 -------------- 1
bug 2 -------------- 3
bug 3 -------------- 1
bug 4 -------------- 1

ma requete est la suivante:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT bugs.name, Count(bugs.id) AS CompteDeid
FROM bugs
WHERE (((bugs.id)=1))
GROUP BY bugs.name;

là je récupère ceci:

bug 1 -------------- 2
bug 3 -------------- 1
bug 4 -------------- 1

et moi ce que je voudrais c'est

bug 1 -------------- 2
bug 2 -------------- 0
bug 3 -------------- 1
bug 4 -------------- 1

je ne peux pas utiliser de jointure puisque c'est dans la meme table.

savez vous comment faire?