I get duplicate data in inner join with 3 tables

Question

I have three tables: Customers, Receipts, Salary

Customers
 Id    Name    
  1    john

Receipts
Id    Amount 
1      500
2      250
3      600
4      700

Salary
Id   Amount 
1    300
2    300
3    680

Id like to find sum of salary and receipts related to john. I thought it's simple sql statement like:

select  cus.Name, sum(sal.Amount) as Salary, sum(re.Amount) as Recieved 
from Customers as cus inner join Salaries as sal on cus.Id = sal.Id
inner join Receipts as re on cus.Id = re.Id
where cus.Id= 1
Group by cus.Name

but I found the result so bigger

Name    Salary   Recieved
john    6150      4320

When I write the query without sum:

select  cus.Name, sal.Amount as Salary, re.Amount as Recieved 
    from Customers as cus inner join Salaries as sal on cus.Id = sal.Id
    inner join Receipts as re on cus.Id = re.Id
    where cus.Id= 1

I get these duplicate records

    john    500 100
    john    500 300
    john    500 680
    john    250 100
    john    250 300
    john    250 680
    john    600 100
    john    600 300
    john    600 680
    john    700 100
    john    700 300
    john    700 680

why the inner join behavior like this? I expect to get the records one time but it repeated 4 times! Did I have to try sub-select queries?

where is `CustomerOrSupplier` column in `Customers` table? And Add your expected output — Jay Shankar Gupta, Apr 16 '18 at 19:08
Umm, no, you didn't get the results you say you got with the sample data you have posted. Please post a script that reproduces the results you are getting. — Tab Alleman, Apr 16 '18 at 19:09
Possible duplicate of [SQL JOIN and different types of JOINs](https://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins) — Tab Alleman, Apr 16 '18 at 19:13
I make miss typing. CustomerOrSupplier is wrong. I edit the srcript — khorshid, Apr 16 '18 at 19:14
The question is still wrong. You are joining on ID, yet there is only one Receipt with ID=1, and only one Salary, so you would not be getting multiple rows if that were true. — Tab Alleman, Apr 16 '18 at 19:19
The sample data must be wrong or incomplete because there is no value 100 in any of your amount columns, or any other column. — Zorkolot, Apr 16 '18 at 19:21

score 0 · Answer 1 · answered Apr 16 '18 at 19:10

0

The query looks right.

http://www.sqlfiddle.com/#!9/1b5157/1

There's a fiddle with your approximate query and it works as expected. So as the person before said, since you have CustomerOrSupplier, you might be joining to the wrong table

answered Apr 16 '18 at 19:10

Eric Yang

2,678
1
12
18

score 0 · Answer 2 · answered Apr 16 '18 at 19:10

select  cus.Name, 
        sum(sal.Amount) as Salary, 
        sum(re.Amount) as Recieved 
from Customers as cus 
inner join Salary as sal 
on cus.Id = sal.Id
inner join 
Receipts as re 
on cus.Id = re.Id
Group by cus.Name;

Demo

http://sqlfiddle.com/#!18/956fb/10

score 0 · Answer 3 · edited Apr 16 '18 at 20:16

0

I see that the issue happens to be one of the tables names.

Change the following:

from Customers as cus inner 
join Salaries as sal 
on cus.Id = sal.Id

to:

from Customers as cus inner 
join Salary as sal 
on cus.Id = sal.Id

edited Apr 16 '18 at 20:16

Maciej Jureczko

1,560
6
19
23

answered Apr 16 '18 at 19:26

Michael Main

1
1

I get duplicate data in inner join with 3 tables

3 Answers3