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 | | | | | |