CODEWITHSUNDEEP
mysqlsql
MichelReap
MichelReap

Reputation: 5770

Aggregate function with many-to-many inner join

I have a foo table which has a one-to-many relationship with the BAR table. FOO also has a many-to-many relationship with QUX.

To illustrate:

FOO
+--------+
| fid    | 
+--------+
|    1   |
|    2   |
|    3   |
|    4   |
+--------+


BAR
+------+-------+-----+
| bid  | cost  | fid |
+------+-------+-----+
|    1 |     2 |  1  |  
|    2 |     4 |  1  |
|    3 |     8 |  2  |
|    4 |    42 |  3  |
|    5 |    21 |  3  |
|    6 |     5 |  4  |
|    7 |    98 |  2  |
|    8 |    30 |  4  |
+------+-------+-----+

FOOQUX
+------+------+
| fid  | qid  |
+------+------+
|    1 |    2 |
|    2 |    1 |
|    2 |    3 |
|    4 |    1 |
+------+------+

QUX
+------+
| qid  |
+------+
|    1 |
|    2 | 
|    3 |
|    4 |
+------+

Now I want to calculate the sum of BAR.cost for every FOO entry, but I want to filter by QUX in terms of "FOO has an association with any element of QUX"

I tried using this sentence:

SELECT fid, sum(cost)
FROM FOO
INNER JOIN BAR on BAR.fid = FOO.fid
LEFT JOIN FOOQUX ON FOOQUX.fid = FOO.fid

But this returns an entry for each instance of FOO in FOOQUX, so in the case of fid=2 (which has two QUX associated to it) we'll have double the amount of cost than in reality.

Maybe this is a simple problem but I'm struggling on how to solve it.

Upvotes: 0

Views: 1216

Answers (1)

user1300630
user1300630

Reputation:

I think this will help You. Group by foo.fid will guarantee that every foo records will appear only once with the sum of its bar-costs. Having filters the aggregated records: a record will returned only if the FOOQUX table contains its fid.

SELECT fid, SUM(cost)
FROM foo JOIN bar on bar.fid=foo.fid
GROUP BY foo.fid
HAVING foo.fid IN (SELECT fid FROM FOOQUX)

Upvotes: 1

Related Questions