CODEWITHSUNDEEP
mysql
Amiga500
Amiga500

Reputation: 6131

Order by inside the LEFT JOIN

I am trying to write a query. I got it work half way, but I am having problems with the LEFT JOIN.

I have three tables:

User will always have one user_preference, but it can have many or no entries in the user_subscription_plan

If the user has no entry in the user_subscription_plan, or if he has only one then my sql works. If I have more then one, then I have issue. In the case of two entries, how can I make it to return the last one entered? I tried playing with ORDER statement, but it does not work as expected. Somehow I get empty rows.

Here is my query:

SELECT u.id AS GYM_USER_ID, subscription_plan.id AS subscriptionId, up.onboarding_completed AS CompletedOnboarding,
  (CASE
     WHEN ((up.onboarding_completed = 1)
      AND (ISNULL(subscription_plan.id)))
      THEN 'freemiun'
     WHEN (ISNULL(up.onboarding_completed)
      AND (ISNULL(subscription_plan.id)))
      THEN 'not_paying'
     END) AS subscription_status
 FROM user AS u
    INNER JOIN user_preferences up ON up.user_id = u.id
    LEFT JOIN (
        SELECT * FROM user_subscription_plan AS usp ORDER BY usp.id DESC LIMIT 1
            ) AS subscription_plan ON subscription_plan.user_id = u.id
GROUP BY u.id;

If I run it as it is, then subscription_plan.id AS subscriptionId is always empty. If I remove the LIMIT clause, then its not empty, but I am still getting the first entry, which is wrong in my case

I have more CASE's to cover, but I can't process until I solve this problem.

Upvotes: 0

Views: 61

Answers (1)

momo
momo

Reputation: 141

Please try to use "max(usp.id)" that "group by subscription_plan.user_id" instead of limit 1.

If you limit 1 in the subquery, the subquery's result will always return only 1 record (if the table has data). So the above query can be rewritten like this.

Sorry, I didn't test, because I don't have data, but please try, hope this can help.

SELECT 
    u.id AS GYM_USER_ID,
    subscription_plan.id AS subscriptionId,
    up.onboarding_completed AS CompletedOnboarding,
    (CASE
        WHEN
            ((up.onboarding_completed = 1)
                AND (ISNULL(subscription_plan.id)))
        THEN
            'freemiun'
        WHEN
            (ISNULL(up.onboarding_completed)
                AND (ISNULL(subscription_plan.id)))
        THEN
            'not_paying'
    END) AS subscription_status
FROM
    user AS u
        INNER JOIN
    user_preferences up ON up.user_id = u.id
        LEFT JOIN
    (SELECT 
        usp.user_id, MAX(usp.id)AS id
    FROM
        user_subscription_plan AS usp
    GROUP BY usp.user_id) AS subscription_plan ON subscription_plan.user_id = u.id;

Upvotes: 1

Related Questions