Want to JOIN fourth table in query

Question

I have four tables:

1. mls_category
2. points_matrix
3. mls_entry
4. bonus_points

My first table (mls_category) is like below:

*--------------------------------*
| cat_no |  store_id | cat_value |
*--------------------------------*
|   10   |    101    |     1     |
|   11   |    101    |     4     |
*--------------------------------*

My second table (points_matrix) is like below:

*----------------------------------------------------*
| pm_no |  store_id | value_per_point | maxpoint     |
*----------------------------------------------------*
|   1   |    101    |       1         |      10      |
|   2   |    101    |       2         |      50      |
|   3   |    101    |       3         |      80      |
*----------------------------------------------------*

My third table (mls_entry) is like below:

*-------------------------------------------*
| user_id |  category | distance |  status  |
*-------------------------------------------*
|    1    |     10    |    20    | approved |
|    1    |     10    |    30    | approved |
|    1    |     11    |    40    | approved |
*-------------------------------------------*

My fourth table (bonus_points) is like below:

*--------------------------------------------*
| user_id |  store_id | bonus_points | type  |
*--------------------------------------------*
|    1    |    101    |      200     | fixed |
|    2    |    102    |      300     | fixed |
|    1    |    103    |       4      |  per  |
*--------------------------------------------*

Now, I want to add bonus points value into the sum of total distance according to the store_id, user_id and type.

I am using the following code to get total distance:

SELECT MIN(b.value_per_point) * d.total_distance FROM points_matrix b 
JOIN 
(
    SELECT store_id, sum(t1.totald/c.cat_value) as total_distance FROM mls_category c 
    JOIN 
    (
        SELECT SUM(distance) totald, user_id, category FROM mls_entry 
        WHERE user_id= 1 AND status = 'approved' GROUP BY user_id, category
    ) t1 ON c.cat_no = t1.category
) d ON b.store_id = d.store_id AND b.maxpoint >= d.total_distance

The above code is correct to calculate value, now I want to JOIN my fourth table.

This gives me sum (60*3 = 180) as total value. Now, I want (60+200)*3 = 780 for user 1 and store id 101 and value is fixed.

Answer 1

i think your query will be like below

SELECT Max(b.value_per_point)*( max(d.total_distance)+max(bonus_points)) FROM mls_point_matrix b
JOIN
(
   SELECT store_id, sum(t1.totald/c.cat_value) as total_distance FROM mls_category c
   JOIN
   (
       SELECT SUM(distance) totald, user_id, category FROM mls_entry
       WHERE user_id= 1 AND status = 'approved' GROUP BY user_id, category
   ) t1 ON c.cat_no = t1.category group by store_id
) d ON b.store_id = d.store_id inner join bonus_points bp on bp.store_id=d.store_id

DEMO fiddle