Inner Join for four tables is not working?

Question

I have four tables mls_user, mls_category, mls_entry, and bonus_point.

Following are the tables:

mls_user

*--------------------------*
| id | store_id |   name   |
*--------------------------*
| 1  |   101    | Santosh  |
| 2  |   101    |   Aman   |
| 3  |   102    |  Ankit   |
| 4  |   101    |  Suman   |
| 5  |   101    |  Ramesh  |
*--------------------------*

mls_category

*----------------------------------------*
| cat_no | store_id | cat_type | cat_val |
*----------------------------------------*
|   1    |   101    | running  |   1     |
|   2    |   101    | cycling  |   4     |
|   3    |   102    | running  |   1     |
|   4    |   102    | swiming  |   2     | 
*----------------------------------------*

mls_entry

*----------------------------------------------------------*
| id | user_id | store_id | category | distance |  status  |
*----------------------------------------------------------*
| 1  |    1    |   101    |    1     |    5     | Approved |
| 2  |    1    |   101    |    1     |    10    | Approved |
| 3  |    1    |   101    |    2     |    50    | Approved |
| 4  |    1    |   101    |    1     |    10    | Approved |
| 5  |    2    |   101    |    1     |    5     | Approved |
*----------------------------------------------------------*

bonus_point

*---------------------------------------------------------*
| bns_id | user_id | store_id | bonus_points | bonus_type |
*---------------------------------------------------------*
|    1   |    1    |    101   |     100      |    fixed   |
|    2   |    2    |    101   |      25      |      %     |
*---------------------------------------------------------*

Now from these four tables, I want below output:

*--------------------------------------------------------------*
|   name   | points | bonus_points | bonus_type | Total_Points |
*--------------------------------------------------------------*
| Santosh  |   25   |     100      |    fixed   |      125     |
|   Aman   |    5   |      25      |      %     |      30      |
|  Suman   |    0   |      0       |      -     |       0      |
|  Ramesh  |    0   |      0       |      -     |       0      |
|  Ankit   |    0   |      0       |      -     |       5      |
*--------------------------------------------------------------*

I am using the following query but this query only shows the value for Santosh and aman

SELECT u.id, u.name, COALESCE(t1.points, 0) AS points, b.bonus_points
FROM mls_user u
LEFT JOIN
    (SELECT e.user_id, 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
     WHERE e.status='approved' AND e.store_id = '101'
     GROUP BY e.user_id ) t1 ON u.id = t1.user_id
INNER JOIN bonus_points b ON b.user_id = u.id
WHERE u.store_id = '101'
ORDER by points DESC

So where I am doing wrong in my query, Please help me in this.

Answer 1

In your bonus_points table you have only two entry related to user 1 and 2 (Santosh and aman) if you use INNER JOIN then you obtain only these user

in this case you need left JOIN for bonus_points Too

SELECT u.id, u.name, COALESCE(t1.points, 0) AS points, ifnull(b.bonus_points,0) bonus_point
FROM mls_user u
LEFT JOIN  (
    SELECT e.user_id, 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
     WHERE e.status='approved' 
     AND e.store_id = '101'
     GROUP BY e.user_id 
     ) t1 ON u.id = t1.user_id
LEFT  JOIN bonus_points b ON b.user_id = u.id
WHERE u.store_id = '101'
ORDER by points DESC