Nrthugu

I am using six table for users and I want to combine all table and show one result to show all values with some condition.

Following are my tables:

1) mls_stores

*----------------------------*

|   store_id |  store_title  |

*----------------------------*

|     1001   |  ajmar-jaipur |

|     1002   |  dwarka-delhi |

*----------------------------*

2) mls_category

*-------------------------------------------*

 | cat_no | store_id |  cat_value | cat_type |

 *-------------------------------------------*

 |  20    |   1001   |     1      | running  |

 |  21    |   1001   |     4      | cycling  |

 |  22    |   1002   |     1      | running  |

 |  23    |   1002   |     2      | swmining |

 *-------------------------------------------*

3) mls_points_matrix

*----------------------------------------*

| store_id | value_per_point |  maxpoint |

*----------------------------------------*

|   1001   |       1         |    10     |

|   1001   |       2         |    20     |

|   1001   |       4         |    50     |

|   1002   |       1         |    20     |

|   1002   |       4         |    30     |

*----------------------------------------*

4) mls_user

*--------------------------*

| id |  store_id |  name   |

*--------------------------*

|  1 |  1001     | sandeep |

|  2 |  1001     | jagveer |

|  3 |  1002     | gagan   |

*--------------------------*

5) bonus_points

   *---------------------------------------------------*

   | user_id |  store_id |  bonus_points |  bonus_type |

   *---------------------------------------------------*

   |    1    |    1001   |     10        |   fixed     |

   |    3    |    1002   |      2        |     %       |

   *---------------------------------------------------*

6) mls_entry

*-------------------------------------------------------*

| user_id | store_id |  category |  distance |  status  |

*-------------------------------------------------------*

|   1     |   1001   |    20     |    20     | approved |

|   1     |   1001   |    21     |    40     | approved | 

|   1     |   1001   |    20     |     5     | reject   | 

|   2     |   1001   |    21     |    40     | approved | 

|   3     |   1002   |    22     |    10     | approved | 

|   3     |   1002   |    23     |    20     | approved |

*-------------------------------------------------------*

Now I want output as below:

*-----------------------------------------------------------------------------------*

| Name    |  Entries   | Points Earned | Bonus Points | Total Points | Total Amount |

*-----------------------------------------------------------------------------------*

| Sandeep | running(1) |       20      |      10      |     30       |     120      |

|         | cycling(1) |               |              |              |              |    

*-----------------------------------------------------------------------------------*

| Jagveer | cycling(1) |       10      |      0       |     10       |     10       |    

*-----------------------------------------------------------------------------------*  

I am using following code:

SELECT

    u.name,

    ROUND(COALESCE(t1.points, 0)) AS points,

    ROUND(COALESCE(b.bonus_points, 0)) AS bonus_points,

    ROUND(COALESCE(t1.points, 0) + COALESCE(b.bonus_points, 0)) AS total_points

FROM mls_user u

LEFT JOIN

(

    SELECT e.user_id, e.status, SUM(e.distance / c.cat_value) AS points

    FROM mls_entry e

    INNER JOIN mls_category c

        ON e.store_id = c.store_id AND e.category = c.cat_no

    GROUP BY e.user_id

    HAVING e.status='approved'

) t1

    ON u.id = t1.user_id

LEFT JOIN bonus_points b

    ON u.id = b.user_id

WHERE u.store_id = '1001'

ORDER BY

    total_points DESC

Now, how do calculate total amount?
My total amount will be for Sandeep 30X4(it is coming from point matrix) = 120
same like for jagveer, the total amount for jagveer 10X2 = 20.