Getting duplicate value when using join

Posted on

Question :

I have 4 tables accordingly that i want join query. Each contact Center id have one annual budget value in second table. Each contact center id have multiple row value in remaining two table i.e RI_DETAILS & F_RI_DETAILS. I want to fetch all the records against that contact center id

1)Cost_centers

id  centers
155 20R23IND
154 19R11IND
153 18R22IND
152 18R21IND
151 18R12IND

2)ANNUAL_BUDGET_BUDGET_CENTER

ID  BUDGET_ID     BUDGET_NAME   BUDGET_ANNUAL_AMOUNT    START_DATE  END_DATE

155  155           20R23IND           5000              01-01-2020  31-12-2020
154  154           19R11IND           8000              01-01-2020  31-12-2020
153  153           18R22IND           1000              01-01-2020  31-12-2020
152  152           18R21IND           12000             01-01-2020  31-12-2020

3)RI_DETAILS

ID     DATE     COST_CENTER  AMOUNT    LEAD_ID
276 07-04-2020      155      18.257     1324
202 07-04-2020      155      18.257     1223
206 07-04-2020      155      18.257     1223
208 07-04-2020      153      17.257     1225

4)F_RI_DETAILS

ID     DATE     COST_CENTER  AMOUNT   FINANCEADD_ID
59  07-04-2020      155     1826.363        71
60  07-04-2020      154      826.363        78

I am using below query of join

 select b.id as ID ,
            b.centers as Cost_Center_Name,
            y.BUDGET_ANNUAL_AMOUNT Annual_Budget,
            r.ID as RI_Id,
            r.AMOUNT as RI_AMOUNT,
            f.ID as Finance_Id,
            f.AMOUNT as Finance_Amount

            from Cost_centers  b
            left outer join ANNUAL_BUDGET_BUDGET_CENTER y on 
            y.BUDGET_ID=b.id
            left outer join RI_DETAILS r on r.COST_CENTER =b.id
            left outer join F_RI_DETAILS f on f.COST_CENTER =b.id
            ORDER BY b.id DESC

Using this query i am getting below output :

ID   Cost_Center_Name   Annual_Budget  RI_Id  RI_AMOUNT  Finance_Id Finance_Amount
155     20R23IND            5000        207      20           59      1826.363
155     20R23IND            5000        208     6.432         59      1826.363
155     20R23IND            5000        276     18.257        59      1826.363
154     19R11IND            5000                 
153     18R22IND            5000                 
152     18R21IND            5000                 

here Finance_id comes 3 times but in database have only one row. SO i want query according that comes only one . Query suitable for RI and FI table both i.e table 3 and 4

Please anyone have solution then help me

Answer :

When you join and don’t have a 1:1 relationship, you get multiple rows.

You can GROUP BY and use for all columns that are not in the GROUP BY Aggregation functions like GROUP_CONCAT in the example

Dates should always be saved in the MySQL format, that makes it easier to use date functionas

Schema (MySQL v8.0)

CREATE TABLE Cost_centers (
  `id` INTEGER,
  `centers` VARCHAR(12)
);

INSERT INTO Cost_centers
  (`id`, `centers`)
VALUES
  ('155','20R23IND'),
  ('154','19R11IND'),
  ('153','18R22IND'),
  ('152','18R21IND'),
  ('151','18R12IND');

CREATE TABLE ANNUAL_BUDGET_BUDGET_CENTER (
  `ID` INTEGER,
  `BUDGET_ID` INTEGER,
  `BUDGET_NAME` VARCHAR(8),
  `BUDGET_ANNUAL_AMOUNT` INTEGER,
  `START_DATE` VARCHAR(10),
  `END_DATE` VARCHAR(10)
);

INSERT INTO ANNUAL_BUDGET_BUDGET_CENTER
  (`ID`, `BUDGET_ID`, `BUDGET_NAME`, `BUDGET_ANNUAL_AMOUNT`, `START_DATE`, `END_DATE`)
VALUES
  ('155', '155', '20R23IND', '5000', '01-01-2020', '31-12-2020'),
  ('154', '154', '19R11IND', '8000', '01-01-2020', '31-12-2020'),
  ('153', '153', '18R22IND', '1000', '01-01-2020', '31-12-2020'),
  ('152', '152', '18R21IND', '12000', '01-01-2020', '31-12-2020');

CREATE TABLE RI_DETAILS (
  `ID` VARCHAR(14),
  `DATE` VARCHAR(10),
  `COST_CENTER` FLOAT,
  `AMOUNT` INTEGER,
  `LEAD_ID` INTEGER
);

INSERT INTO RI_DETAILS
  (`ID`, `DATE`, `COST_CENTER`, `AMOUNT`, `LEAD_ID`)
VALUES
  ('276','07-04-2020', '155', '18.257', '1324'),
  ('202','07-04-2020', '155', '18.257', '1223'),
  ('206','07-04-2020', '155', '18.257', '1223'),
  ('208','07-04-2020', '153', '17.257', '1225');

CREATE TABLE F_RI_DETAILS (
  `ID` INTEGER,
  `DATE` VARCHAR(10),
  `COST_CENTER` INTEGER,
  `AMOUNT` FLOAT,
  `FINANCEADD_ID` INTEGER
);

INSERT INTO F_RI_DETAILS
  (`ID`, `DATE`, `COST_CENTER`, `AMOUNT`, `FINANCEADD_ID`)
VALUES
  ('59', '07-04-2020', '155', '1826.363', '71'),
  ('60', '07-04-2020', '154', '826.363', '78');

Query #1

SELECT 
    b.id AS ID,
    b.centers AS Cost_Center_Name,
    y.BUDGET_ANNUAL_AMOUNT Annual_Budget,
    GROUP_CONCAT(r.ID) AS RI_Id,
    GROUP_CONCAT(r.AMOUNT) AS RI_AMOUNT,
    GROUP_CONCAT(f.ID) AS Finance_Id,
    GROUP_CONCAT(f.AMOUNT) AS Finance_Amount
FROM
    Cost_centers b
        LEFT OUTER JOIN
    ANNUAL_BUDGET_BUDGET_CENTER y ON y.BUDGET_ID = b.id
        LEFT OUTER JOIN
    RI_DETAILS r ON r.COST_CENTER = b.id
        LEFT OUTER JOIN
    F_RI_DETAILS f ON f.COST_CENTER = b.id


group BY  b.id,b.centers,y.BUDGET_ANNUAL_AMOUNT
ORDER BY b.id DESC;

| ID  | Cost_Center_Name | Annual_Budget | RI_Id       | RI_AMOUNT | Finance_Id | Finance_Amount          |
| --- | ---------------- | ------------- | ----------- | --------- | ---------- | ----------------------- |
| 155 | 20R23IND         | 5000          | 276,202,206 | 18,18,18  | 59,59,59   | 1826.36,1826.36,1826.36 |
| 154 | 19R11IND         | 8000          |             |           | 60         | 826.363                 |
| 153 | 18R22IND         | 1000          | 208         | 17        |            |                         |
| 152 | 18R21IND         | 12000         |             |           |            |                         |
| 151 | 18R12IND         |               |             |           |            |                         |

View on DB Fiddle

Leave a Reply

Your email address will not be published.