Aggregating Using Joins In Sql

Usama Abubakar 0
Viewed 34

Need to understand the sql behavior when fetching records in a relationship model.

I have 2 simple tables.

Table Name : One

enter image description here

Table Name: Two

enter image description here

When I run the query

select o.name, sum(o.value) as Total
from one o
  inner join two t on o.id = t.one_id

I get back the result which is correct

enter image description here

But As soon as i add another record in Table Two link to record number 1 in Table One, the total is doubled to 10 on running the same aggregate query. I know that when running simple query, sql will return 2 records each will refer to the single relationship but how to tackle this problem?

enter image description here

enter image description here

aggregate-functions inner-join join mysql sql
1 Answers

There is no problem. You have 1 to many relationship. Table two has 2 record which both refer to first record of table one. Then because of value 5 which is repeated as much as records in table two.

 SELECT /*record in table two*/ 2  * /*value of first record*/ 5 = 10
Meyssam Toluie0
Related