Q:

How to query multiple tables like this in MYSQL

Suppose I have some tables in my database like that.
The first one is DA table, which has three column and I want to query others table based on PROJECT_ID.

The next one is WORK table. I want to calculate SUM of work_bonus and work_salary by specific da_id.

The next table is the expense table

Next one is product table

The last one is the summary table. I want to print DA like below this table. That means suppose I want to search da based on project_id "15" so I will get two da_id from da table i.e da_id 1 and 2. Now work, expense and product table has da_id field. So I want to print DA summary from work, expense and product table. Here in WORK table DA_ID 1 has two records i.e work_id 1 and 3. I want to total its WORK_BONUS and WORK_SALARY value based on DA_ID that means total WORK_BONUS of DA_ID 1 will be 40 and WORK_SALARY of DA_ID 1 will be 400. Like this, rest table calculation will be same. Here is the final table which I want. So can you please help me to calculate the result in mysql? Thank you

Text version-
DA TABLE
-------------------------------------------------
|   DA_ID     |    PROJECT_ID  |      DA_DATE    |
-------------------------------------------------
|    1        |       15        |       8/7/18   |
-------------------------------------------------
|    2        |       15        |       4/7/18   |
-------------------------------------------------
|    3        |       14        |       6/7/18   |
-------------------------------------------------

WORK TABLE
--------------------------------------------------------------------
|   WORK_ID   |     DA_ID      |    WORK_BONUS  |      WORK_SALARY   |
--------------------------------------------------------------------
|    1        |       1        |       10       |           100      |
--------------------------------------------------------------------
|    2        |       2        |       20       |          200       |
-------------------------------------------------------------------
|    3        |       1        |       30       |          200       |
--------------------------------------------------------------------

EXPENSE TABLE
-----------------------------------------------------
|   EXP_ID    |    DA_ID        |      EXP_TOTAL    |
-----------------------------------------------------
|    1        |       1         |       10          |
-----------------------------------------------------
|    2        |       2         |       20          |
-----------------------------------------------------
|    3        |       1         |       30          |
-----------------------------------------------------

PRODUCT TABLE
-------------------------------------------------------------
|   PRODUCT_ID    |    DA_ID        |      PRODUCT_TOTAL    |
-------------------------------------------------------------
|       1         |       1         |       5               |
-------------------------------------------------------------
|       2         |       2         |       10              |
-------------------------------------------------------------
|       3         |       1         |       15              |
-------------------------------------------------------------

SUMMARY TABLE
----------------------------------------------------------------------------------------
|   SUMMARY_ID    |   DA_ID   |   TOTAL_BONUS  |   TOTAL_EXPENSE   |   TOTAL_PRODUCT   |
----------------------------------------------------------------------------------------
|       1         |     1     |        40      |        400        |         20        |
----------------------------------------------------------------------------------------
|       2         |     2     |        20      |        200        |         10        |
----------------------------------------------------------------------------------------

A:

There are two ways to achive this using query. There may be more appropriate way
Way 1
SELECT D.DA_ID,
    (SELECT SUM(WORK_BONUS) FROM `WORK` W WHERE W.DA_ID = D.DA_ID) as TOTAL_BONUS,
    (SELECT SUM(WORK_SALARY) FROM `WORK` W WHERE W.DA_ID = D.DA_ID) as TOTAL_SALARY,
    (SELECT SUM(EXP_TOTAL) FROM EXPENSE E WHERE E.DA_ID = D.DA_ID) as TOTAL_EXPENSE,
    (SELECT SUM(PRODUCT_TOTAL) FROM PRODUCT P WHERE P.DA_ID = D.DA_ID) as TOTAL_PRODUCT
FROM DA as D left join WORK as W on D.DA_ID = W.DA_ID
WHERE D.PROJECT_ID = 15
GROUP BY D.DA_ID;

Way 2
SELECT D.DA_ID, SUM(WORK_BONUS) as TOTAL_BONUS, SUM(WORK_SALARY) as TOTAL_SALARY, 
    (SELECT SUM(EXP_TOTAL) FROM EXPENSE E WHERE E.DA_ID = D.DA_ID) as TOTAL_EXPENSE,
    (SELECT SUM(PRODUCT_TOTAL) FROM PRODUCT P WHERE P.DA_ID = D.DA_ID) as TOTAL_PRODUCT
FROM DA as D left join WORK as W on D.DA_ID = W.DA_ID
WHERE D.PROJECT_ID = 15
GROUP BY D.DA_ID;

