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?
You're trying to compute too many aggregates in one query.
Your tables B, C, D, and E are produced Cartesian Products against each other. Suppose the given row in A matches:
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: