CODEWITHSUNDEEP
mysql
Storm Spirit
Storm Spirit

Reputation: 1510

How to get the next value from inner join SQL

This question is continue of this here, since they don't want to answer without opening new.

This is my tables:

customer_profiles

enter image description here

friend_levels

enter image description here

so I have a query that get what level he's from, so when he got points = 168 then he will getGreat Friend so this is the SQL that I've already have.

SELECT s.*
FROM customer_profiles t
INNER JOIN friend_levels s ON(t.friend_points >= s.points_needed)
WHERE s.points_needed = (SELECT max(f.points_needed)
                        FROM friend_levels f
                        WHERE t.friend_points >= f.points_needed)
AND t.user_id = $user_id

and result like this

enter image description here

so my question is how to get the next value of it in order to use it like this? For ex. If I am on the Great Friend level, then I have to get the Best Friend level.

enter image description here

Upvotes: 0

Views: 150

Answers (1)

Sameer Mirji
Sameer Mirji

Reputation: 2245

This should be simple:

SELECT (SELECT min(fl.points_needed) 
          FROM friend_levels fl
         WHERE fl.points_needed > cp.friend_points) - cp.friend_points AS points_needed_for_next_level
  FROM customer_profiles cp
 WHERE cp.user_id = $user_id;

Upvotes: 1

Related Questions