CODEWITHSUNDEEP
mysqlsql
concept104
concept104

Reputation: 89

left join produces wrong results

I have three tables as : ab, a, and b Table a and b should have multiple occurrences for the same touid. 

SELECT * FROM ab
tourid  tourname    
------  ------------
   100  hundred     
   110  one ten     
   120  one twenty 


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

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

sql query : 

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

gives the result as:

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

But result should be as : 

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

result of b_sum column is wrong

Upvotes: 2

Views: 122

Answers (3)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

The counts are correct: since you are joining to both tables at once, the results before aggregation will look like this:

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

You get the same row of b for each joined row in a.

When you total up a.filesize, you get 30; when you total up b.filesize, you get 10, explaining the results.

You can get the results that you wish without joins or aggregations - simple subqueries would be sufficient:

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

Upvotes: 1

dagfr
dagfr

Reputation: 2384

In the first table, you have one row : 

tourid  tourname 
 100  hundred

Joining the a table will produce 2 rows :

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

Joining the b table will keep 2 rows : 

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

the good query is : 

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

Upvotes: 3

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You need to add GROUP BY ab.tourid to overcome duplicates, there are repeated results therefore you are getting the sum as twice

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

Upvotes: 1

Related Questions