CODEWITHSUNDEEP
sqloracle-databaseduplicates
Maram A-zaid
Maram A-zaid

Reputation: 55

Duplication of Records due to join a table

As stated in the title, There are record duplications when I try to join a table

let say the main below table is called "Customer"

Month_id customer_name Amount
202012 A 10000
202012 B 569
202012 C 23000

the 2nd table is called "Date"

Year Month_id Time_id
2020 202012 20201201
2020 202012 20201202
2020 202012 20201203
2020 202012 20201204
2020 202012 20201205
2020 202012 20201206
2020 202012 20201207
2020 202012 20201208
2020 202012 20201209
2020 202012 20201210
2020 202012 20201211
2020 202012 20201212
2020 202012 20201213
2020 202012 20201214
2020 202012 20201215
2020 202012 20201216
2020 202012 20201217
2020 202012 20201218
2020 202012 20201219
2020 202012 20201220
2020 202012 20201221
2020 202012 20201222
2020 202012 20201223
2020 202012 20201224
2020 202012 20201225
2020 202012 20201226
2020 202012 20201227
2020 202012 20201228
2020 202012 20201229
2020 202012 20201230
2020 202012 20201231

I tried to join both of them to get the year ( NOTE: I know that I can use substr function to extract the year from mont_id, but I do not want to do that because that was just an example)

select a11.month_id, a11.Customer_name, a11.Amount, a12.year
from customer a11
join Date a12 on a11.month_id = a12.month_id

The result that I expect from the above query is the following enter image description here

unfortunately, I got duplications in rows instead of that. to be more clarified I got 31 records for each customer

EX: Customer A

enter image description here

How can I solve the problem?

Upvotes: 0

Views: 32

Answers (1)

Aleksej
Aleksej

Reputation: 22959

You need to group the dates by month; an easy way could be:

select a11.month_id, a11.Customer_name, a11.Amount, a12.year
from customer a11
join
(
    select month_id, year
    from Date
    group by month_id, year
) a12
on a11.month_id = a12.month_id

Another way, without modifying the join part, could be by simply using DISTINCT

select distinct c.Month_id,c. customer_name, c.Amount, d.year
from customer c
inner join dateTable d
on c.month_id = d.month_id

But notice that this strongly relies on the fact that you just have one row for each customer and month; otherwise you could need to SUM the amounts and things change

Upvotes: 3

Related Questions