Question 20612 in Mysql Question posted in
The official MySQL documentation can be found here.

Duplicate records after multiple table join in SQL – Mysql

I have a table called main_table and it has the following records

cust_id start_date end_date
10001 2022-12-01 2022-12-30
10001 2023-01-03 2023-02-01

I have another table called penalty_a_type and it has the following records

cust_id start_date end_date penalty_a
10001 2022-12-01 2022-12-30 0
10001 2023-01-03 2023-02-01 0

I have another table called penalty_b_type and it has the following records

cust_id start_date end_date penalty_b
10001 2022-12-01 2022-12-30 0
10001 2023-01-03 2023-02-01 4

I want to join these tables so that it looks like

cust_id start_date end_date penalty_a penalty_b
10001 2022-12-01 2022-12-30 0 0
10001 2023-01-03 2023-02-01 0 4

But I am getting the following table

cust_id start_date end_date penalty_a penalty_b
10001 2022-12-01 2022-12-30 0 0
10001 2022-12-01 2022-12-30 0 4
10001 2023-01-03 2023-02-01 0 0
10001 2023-01-03 2023-02-01 0 4

The code I have tried is

SELECT     m.cust_id,
           m.start_date,
           m.end_date,
           pa.penalty_a
           pb.penalty_b
    FROM main_table m
    JOIN penalty_a_type pa
    ON m.cust_id = pa.cust_id
    JOIN penalty_b_type pb
    ON m.cust_id = pb.cust_id
    GROUP BY m.cust_id, m.start_date, m.end_date, pa.penalty_a, pb.penalty_b
Tags:

3

Answers


  1. 0

    cust_id on main table is duplicated.

    If you care about the different start/end dates, then the result is fine. If you don’t care, then remove it from group (and select)

    SELECT     m.cust_id,
           pa.penalty_a
           pb.penalty_b
    FROM main_table m
    JOIN penalty_a_type pa
    ON m.cust_id = pa.cust_id
    JOIN penalty_b_type pb
    ON m.cust_id = pb.cust_id
    GROUP BY m.cust_id,pa.penalty_a, pb.penalty_b
    Login or Signup to reply.
  2. 0

    You can try with ROW_NUMBER and the following result as

    with mt_cte as(SELECT m.cust_id,
       m.start_date,
       m.end_date,
       pa.penalty_a,
       pb.penalty_b,
row_number() over (partition by pa.penalty_a,pb.penalty_b) rn
FROM main_table m
JOIN penalty_a_type pa
ON m.cust_id = pa.cust_id
JOIN penalty_b_type pb
ON m.cust_id = pb.cust_id)
select * from mt_cte where rn = 1

    https://dbfiddle.uk/cvg7jiRu

    Login or Signup to reply.
  3. 0 
    SELECT     m.cust_id,
       m.start_date,
       m.end_date,
       pa.penalty_a,
        pb.penalty_b
FROM main_table m
JOIN penalty_a_type pa
ON m.cust_id = pa.cust_id AND PA.START_DATE=m.START_DATE AND 
pa.END_DATE=m.END_DATE
JOIN penalty_b_type pb 
ON m.cust_id = pb.cust_id AND PB.START_DATE=m.START_DATE AND 
pb.END_DATE=m.END_DATE
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search