MySql query help : joints with sums and counts

joinmysql

I have this database structure:

TBL_A  |  TBL_B  |  TBL_C  |  TBL_D  | TBL_E
-------+---------+---------+---------+----------
id     | id_tbla | id_tbla | id_tbla | id
name   | id_user | id_user | id_user | name_tbla
...    | is_bool |         | weight  | id_user

Here is what I'm trying to achieve :

SELECT 
    a.id, 
    a.name, 
    b.is_bool,
    count(c.id_user) AS nb_views, 
    sum(d.weight) AS total_weight,
    count(distinct e.id_user) AS distinct_users,
FROM TBL_A AS a 
LEFT JOIN (TBL_B AS b) on (b.id_tbla = a.id)
LEFT JOIN (TBL_C AS c) on (c.id_tbla = a.id)
LEFT JOIN (TBL_D AS d) on (d.id_tbla = a.id)
LEFT JOIN (TBL_E AS e) on (e.name_tbla = a.name)
where a.id = 1 and e.id_user = 1

The query is performed but the results (nb_views, total_weight, distinct_users) are wrong. Any idea why?

Best Solution

You're trying to compute too many aggregates in one query.

Enita non sunt multiplicanda praeter necessitatem

(Latin, "entities are not to be multiplied beyond necessity")

Your tables B, C, D, and E are produced Cartesian Products against each other. Suppose the given row in A matches:

  • 3 rows in B
  • 6 rows in C
  • 4 rows in D
  • 1 row in E

The total number of rows in the result is 3 * 6 * 4 * 1 = 72 rows. So your count(c.id_user) is 12 times what it should be, your sum(d.weight) is 18 times what it should be, etc.

The simplest remedy is to compute each of these aggregates in a separate query:

SELECT a.id, a.name, COALESCE(b.is_bool, FALSE) AS is_bool
FROM TBL_A AS a LEFT JOIN TBL_B AS b ON (b.id_tbla = a.id)
WHERE a.id = 1;

SELECT a.id, COUNT(c.id_user) AS nb_views
FROM TBL_A AS a LEFT JOIN TBL_C AS c ON (c.id_tbla = a.id)
WHERE a.id = 1;

SELECT a.id, SUM(d.weight) AS total_weight,
FROM TBL_A AS a LEFT JOIN TBL_D AS d ON (d.id_tbla = a.id)
WHERE a.id = 1;

SELECT a.id, COUNT(DISTINCT e.id_user) AS distinct_users,
FROM TBL_A AS a LEFT JOIN TBL_E AS e 
  ON (e.name_tbla = a.name AND e.id_user = 1)
WHERE a.id = 1;
Related Question