Mysql, when I join one table,

Question

I basically have a table that contains the counts for each date. I want to create a query that gives me the total number of calculations for the entire table, as well as the total for yesterday. But when I try to join the table twice, the aggregates are disabled. Below you can reproduce the results.

CREATE TABLE a (id int primary key); CREATE TABLE b (a_id int, b_id int, date date, count int, primary key (a_id,b_id,date)); INSERT INTO a VALUES (1); INSERT INTO b VALUES (1, 1, UTC_DATE(), 5); INSERT INTO b VALUES (1, 2, UTC_DATE(), 10); INSERT INTO b VALUES (1, 1, UTC_DATE()-1, 7); INSERT INTO b VALUES (1, 2, UTC_DATE()-1, 12); SELECT A.id,SUM(B.count) AS total_count,SUM(Y.count) AS y FROM a AS A LEFT JOIN b AS B ON (B.a_id=A.id) LEFT JOIN b AS Y ON (Y.a_id=A.id AND Y.date=UTC_DATE()-1) GROUP BY A.id; Results in: +----+-------------+------+ | id | total_count | y | +----+-------------+------+ | 1 | 68 | 76 | +----+-------------+------+ The correct result should be: +----+-------------+------+ | id | total_count | y | +----+-------------+------+ | 1 | 34 | 22 | +----+-------------+------+ 

What's going on here? Is this a bug in mysql, or I don't understand how connections work.

Answer 1

No, this is not a bug in MySQL.

Your JOIN conditions generate "duplicate" lines. (Remove aggregate functions and GROUP BY, and you will see what happens.

This row from table "a" corresponds to four rows from table "b". This is all beautiful and good. But when you add a join to the third table ("y"), each row returned from this third table "y" (two rows) is "mapped" to each row from table "b" ... so you keep in common complexity eight lines in your result set. (That's why the "total_count" doubles.)

To get the result set you specified, you do not need to join this table “b” a second time. Instead, simply use a conditional test to determine whether to include this “score” in “y” or not.

eg.

 SELECT a.id , SUM(b.count) AS total_count , SUM(IF(b.date=UTC_DATE()-1 ,b.count,0)) AS y FROM aa LEFT JOIN bb ON (b.a_id=a.id) GROUP BY a.id; 

---

Note that the MySQL IF expression can be replaced with the equivalent ANSI CASE expression to improve portability:

  , SUM(CASE WHEN b.date=UTC_DATE()-1 THEN b.count ELSE 0 END) AS y 

---

If you want to make a JOIN in this "b" table for the second time, you need the JOIN condition to be such that the row from "y" matches at most one row from "b", so that no duplicates are entered. So you basically need a join condition to include all columns in the primary key.

(Note that the predicates in the join condition for table "y" ensure that each of "y" matches no more than one row from "b"):

 SELECT a.id , SUM(b.count) AS total_count , SUM(y.count) AS y FROM aa LEFT JOIN bb ON b.a_id=a.id LEFT JOIN by ON y.a_id = b.a_id AND y.b_id = b.b_id AND y.date = b.date AND y.date = UTC_DATE()-1 GROUP BY a.id; 

(To get the first statement to return an identical result set with potential NULL instead of zero, you need to replace the constant '0' in the IF expression with "NULL".

  , SUM(IF(b.date=UTC_DATE()-1 ,b.count,NULL)) AS y 

Answer 2

 SELECT A.id,b_count AS total_count,y_count as y FROM a AS A LEFT JOIN (select a_id,SUM(B.Count) b_count from b group by B.A_id) AS B1 ON (B1.a_id=A.id) LEFT JOIN (select a_id,SUM(Count) y_count from b where date=UTC_DATE()-1 group by B.A_id) AS Y ON (Y.a_id=A.id) 

SQLFiddle Demo