Pfthb

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.