CODEWITHSUNDEEP
sqlmysqljoin
Harsha Lingampally
Harsha Lingampally

Reputation: 140

Need to retrieve latest instances of a user from a table

These are the two tables I have at hand,

Challenges

id | name

Challenges_Users

user_id | challenge_id | started_at | completed_at

each instance in the challenges_users table is a record of user accepting a challenge and its state(state based on the started_at and completed_at timestamps)

Also, a user can take up a challenge multiple times (not simultaneously, but can take it again after the first instance is completed). So, the table might look like this for a single challenge.

id | challenge_id | started_at | completed_at

2831 | 4 | 2010-12-23 00:00:00 | 2010-12-29 15:42:41

2834 | 4 | 2010-12-29 15:46:53 | NULL

Now, I need to retrieve the list of all the challenges from the challenges table and their state of the latest instance from the challenges_users table.

States are: if no instance for a challenge in challenges_users table => unstarted if completed_at is null and started_at is not null => in progress if completed_at is not null and started_at is not null => completed

I am having 2 problems:

  1. How to do a simple join such that all the challenges which dont have any instances for a particular user_id in challeges_users also get included in the result set.

  2. How do return a result set such that it only has record with latest started_at.

Any help is greatly appreciated. Thanks!

Upvotes: 2

Views: 67

Answers (1)

Michael Goldshteyn
Michael Goldshteyn

Reputation: 74370

Here is a step in the right direction (untested), if I understood what you are trying to do:

SELECT C.ID, C.NAME, CU.USER_ID, CU.STARTED_AT
FROM CHALLENGES AS C 
LEFT JOIN CHALLENGES_USERS AS CU
ON CU.CHALLENGE_ID=C.ID 
  AND CU.STARTED_AT=(
    SELECT MAX(CU2.STARTED_AT) 
    FROM CHALLENGES_USER CU2 
    WHERE CU2.CHALLENGE_ID=C.CHALLENGE_ID)

Upvotes: 2

Related Questions