MySQL 3 tables with 2 join and 1 non join table in one query-CodePudding

Good day,

Having problems with querying this type of scenario.

Sample scenario: I have 3 tables named tbl_customer, tbl_amount, tbl_expense

tbl_customer has fields named customer_id, name, address, date_added

tbl_amount has fields named amount_id, customer_id, amount, date_added

tbl_expense has fields named expense_id, amount, date_added

I want to merge all the table in one result base on the date_added value. I have this query but its not working

SELECT sum(tbl_amount.amount) as daily_total, tbl_amount.date_added, sum(tbl_expense.amount) as daily_expenses FROM tbl_customer LEFT JOIN tbl_amount on tbl_amount.customer_id = tbl_customer.customer_id RIGHT JOIN tbl_expense on tbl_expense.date_added = tbl_amount.date_added GROUP BY tbl_amount.date_added

result was all the amounts was multiplied and not the expected daily total amount.

tbl_customer

customer_id | name | address | date_added

123 | Jane Doe | Sample Address | 2022-08-12

456 | John | Address 2 | 2022-08-12

tbl_amount

amount_id | customer_id | amount | date_added

1 | 123 | 1000 | 2022-08-12

2 | 456 | 2000 | 2022-08-12

tbl_expense

expense_id | amount | date_added

1 | 250 | 2022-08-12

2 | 350 | 2022-08-12

running the code I use. this is the result, which is wrong

total_total | daily_expenses | date_added

6000 | 1200 | 2022-08-12

whereas the correct value should be

3000 | 600 | 2022-08-12

CodePudding user response：

I have question on total expense amount. It should be 550 as per your data. You can change joins based on your requirement. But I think INNER JOIN will be the case.

You can try this

-- create
CREATE TABLE tbl_customer(
customer_id  INT ,
name  VARCHAR(50) NOT NULL,
address  VARCHAR(100) NULL,
date_added DateTIME
);
INSERT INTO tbl_customer (customer_id, name, address, date_added) values (123 , 'Jane Doe','Sample Address','2022-08-12');
INSERT INTO tbl_customer (customer_id, name, address, date_added) values (456 , 'John' , 'Address 2','2022-08-12');

CREATE TABLE tbl_amount(
  amount_id INT,
  customer_id INT,
  amount INT,
  date_added DateTIME
);

INSERT INTO tbl_amount (amount_id, customer_id , amount, date_added) values (1, 123, 1000, '2022-08-12');
INSERT INTO tbl_amount (amount_id, customer_id , amount, date_added) values (2, 456, 2000, '2022-08-12');

CREATE TABLE tbl_expense(
  expense_id INT,
  amount INT,
  date_added DateTIME
);
INSERT INTO tbl_expense (expense_id, amount, date_added) values (1, 250, '2022-08-12');
INSERT INTO tbl_expense (expense_id, amount, date_added) values (2, 300, '2022-08-12');

SELECT SUM(A.amount) as daily_total, A.date_added, E.daily_expenses 
FROM tbl_customer C
INNER JOIN tbl_amount A on A.customer_id = C.customer_id 
INNER JOIN (
  SELECT SUM(E.amount) as daily_expenses, E.date_added
  FROM tbl_expense E
  GROUP BY E.date_added) E on E.date_added = A.date_added 
GROUP BY A.date_added;

Let me know if you still have any query.