Requete sql retourne un mauvais résultat

[ramoucha23]

Bonjour
j'ai une requete qui répète un ligne plusieurs fois je ne sais pas pourquoi!

C'est mon requete , si veux pouvez m'aider en suivant la logique puisque je sais que la requete est un peu compliquée

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
 
SELECT   floor((a.created-c.created)*24)       || ' HOURS ' ||        mod(floor((a.created-c.created)*24*60),60)      || ' MINUTES ' ,a.pkey AS AssignedGroupResponseTime,a.pkey,d.newString AS AssignedGroup 
 FROM 
( SELECT g.created,g.issueid AS groupid1, ji.pkey ,ci.newString FROM   changegroup g JOIN   jiraissue ji ON (ji.id = g.issueid) JOIN   changeitem ci ON (ci.groupid = g.id)
 WHERE (g.created, ji.pkey) IN ( SELECT min(g.created) ,ji.pkey 
 FROM   changegroup g 
JOIN   changeitem ci ON (ci.groupid = g.id) 
JOIN   jiraissue ji ON (ji.id = g.issueid) 
JOIN   project p ON (p.id = ji.project) 
JOIN   priority pr ON (pr.id = ji.priority)
 WHERE  ci.FIELD = 'Group' 
AND  ci.oldString LIKE 'Triage' 
 AND  p.pname = 'project'
 AND pr.pname='P3' 
AND  ji.created BETWEEN '01/03/2011' AND  '16/08/2011' GROUP BY ji.pkey )) a LEFT JOIN 
  ( SELECT ji.created, ji.id AS groupid2 ,ji.pkey ,ci.newString     
 FROM   jiraissue ji     JOIN   changegroup g ON (g.issueid = ji.id)  
   JOIN   changeitem ci ON (ci.groupid = g.id) 
    JOIN   project p ON (p.id = ji.project)  
   WHERE  p.pname = 'project'   
  AND ci.FIELD = 'Group'   
  AND  ci.oldString LIKE 'Triage'    
  AND  ji.created BETWEEN '01/03/2011' AND  '16/08/2011'   ) b
 ON ( a.pkey = b.pkey)    
LEFT JOIN (   SELECT g.created, g.issueid AS groupid1 ,ji.pkey ,ci.newString     FROM   changegroup g  
   JOIN   jiraissue ji ON (ji.id = g.issueid)      
JOIN   changeitem ci ON (ci.groupid = g.id)    
 WHERE (g.created, ji.pkey) IN
 (  SELECT min(g.created) ,ji.pkey     FROM   changegroup g   
  JOIN   changeitem ci ON (ci.groupid = g.id)  
   JOIN   jiraissue ji ON (ji.id = g.issueid)    
   JOIN   project p ON (p.id = ji.project)  
   JOIN   priority pr ON (pr.id = ji.priority)    
   WHERE ci.FIELD='assignee'    
   AND ci.newString IS NOT NULL      
   AND  p.pname = 'project' 
    AND pr.pname='P3'   
   AND ji.created BETWEEN '01/03/2011' AND  '16/08/2011'   
   GROUP BY ji.pkey)) c     
  ON (c.pkey = a.pkey)  
    LEFT JOIN (   
    SELECT g.created, g.issueid AS groupid1 ,ji.pkey ,ci.newString    
    FROM   changegroup g  
    JOIN   jiraissue ji ON (ji.id = g.issueid)   
    JOIN   changeitem ci ON (ci.groupid = g.id)    
    WHERE (g.created, ji.pkey) 
    IN (     SELECT min(g.created) ,ji.pkey    
    FROM   changegroup g    
    JOIN   changeitem ci ON (ci.groupid = g.id)  
    JOIN   jiraissue ji ON (ji.id = g.issueid)  
    JOIN   project p ON (p.id = ji.project)  
   JOIN   priority pr ON (pr.id = ji.priority)      
   WHERE ci.FIELD='assigned group'    
    AND ci.newString IS NOT NULL    
   AND  p.pname = 'project'   
    AND pr.pname='P3'   
    AND  ji.created BETWEEN '01/03/2011' AND  '16/08/2011'    
   GROUP BY ji.pkey)) d   
   ON (d.pkey = c.pkey);

[boussafi]

sans rentrer dans le detaille de ta requete, si tu as des lignes qui se repetent(identiques) tu utilise

Code :

SELECT DISTINCT.....

[Yanika_bzh]

Le DISTINCT est un pansement !

Il faut analyser la requete pour savoir ce qui provoque les retours multiples (Condition de jointure, prédicats érronés, mauvaises conditions de regroupement...).