Left join produziert falsche Ergebnisse

Question

Ich habe drei Tabellen wie:

ab, a und b

Tabelle a und b sollten mehrere Vorkommen für dieselbe tourid haben.

SELECT * FROM ab
tourid  tourname    
------  ------------
   100  hundert    
   110  zehn     
   120  zwanzig 

 select * from a;
imageid  tourid  filesize  
-------  ------  ----------
      1     100          10
      2     100          20

SELECT * FROM b
   uid  tourid  filesize  
------  ------  ----------
     5     100           5

SQL-Abfrage:

SELECT 
a.tourid, 
SUM(a.filesize) AS a_sum,
SUM(b.filesize) AS b_sum
FROM ab
LEFT JOIN a ON a.tourid=ab.tourid
LEFT JOIN b ON b.tourid=ab.tourid
WHERE ab.tourid=100

liefert das Ergebnis:

tourid   a_sum   b_sum  
------  ------  --------
   100      30        10

Aber das Ergebnis sollte sein:

tourid   a_sum   b_sum  
------  ------  --------
   100      30        5

Das Ergebnis der Spalte b_sum ist falsch

Answer 1

Im ersten Tabelle hast du eine Zeile :

tourid  tourname 
 100  hundred     

Wenn du die a Tabelle verbindest, bekommst du 2 Zeilen :

tourid  tourname    imaageid  filesize
 100    hundred     1          10
 100    hundred     2          20

Die Verbindung mit der b Tabelle wird 2 Zeilen behalten :

 tourid  tourname    imaageid  filesize   uid   filesize
 100    hundred     1          10         5       5
 100    hundred     2          20         5       5

Die gute Abfrage ist :

select tourid, sum_a, sum_b 
from ab
left join (select tourid, sum(filesize) as sum_a from a group by tourid) a on ab.tourid = a.tourid
left join (select tourid, sum(filesize) as sum_b from b group by tourid) b on ab.tourid = b.tourid

Answer 2

Sie müssen GROUP BY ab.tourid hinzufügen, um Duplikate zu vermeiden, da es wiederholte Ergebnisse gibt und Sie die Summe daher zweimal erhalten

SELECT 
a.tourid, 
SUM(a.filesize) AS a_sum,
SUM(b.filesize) AS b_sum
FROM ab
LEFT JOIN a ON a.tourid=ab.tourid
LEFT JOIN b ON b.tourid=ab.tourid
WHERE ab.tourid=100
GROUP BY ab.tourid

Answer 3

Die Zählungen sind korrekt: Da Sie beide Tabellen gleichzeitig verbinden, sehen die Ergebnisse vor der Aggregation so aus:

tourid  a.filesize b.filesize
------  ---------- ----------
  100         10          5
  100         20          5

Sie erhalten für jede verbundene Zeile in a dieselbe Zeile von b.

Wenn Sie a.filesize zusammenzählen, erhalten Sie 30; wenn Sie b.filesize zusammenzählen, erhalten Sie 10, was die Ergebnisse erklärt.

Sie können die gewünschten Ergebnisse auch ohne Joins oder Aggregationen erhalten - einfache Unterabfragen wären ausreichend:

SELECT
    100
,   (SELECT SUM(filesize) FROM a WHERE tourid=100) as sum_a
,   (SELECT SUM(filesize) FROM b WHERE tourid=100) as sum_a