MySql - Query and combine different conditions

Question

I actually struggled to come up with a decent title for my question.

I have a table that keeps a log of when users get to a level of a particular game. Levels go from 1 to 5 but I'm particularly interested in knowing how long they got stuck in level 3 (for any game).

My table (gamehistorylog):

gameid (int)
level (int)
dateofchange (date)

I'm obviously not interested in games where the level is less than 3. I can use datediff() but I'm not sure how to form the query to get the combined data of students that are at level 3 still (how long have they been there?) and those that passed it already at levels 4 & 5 (how long did the spend in level 3)? Every time a level is passed, a table record is generated with the new level and the date for the specific game.

Expected output is just a list of all games on the table that are at or above level 3 and time spent on level 3.

I have another table that keeps the official game status so i can join with that table and query the date of the most recent date for games where status = 3.

select l.gameid as gid,  
DATEDIFF(NOW(), max(dateofchange)) as datediff 
from gamehistorylog l 
join games g on g.gameid = l.gameid 
where g.status = 3 and l.level = 3 ;

This will get me the data for all instances where the highest level is 3. I don't know how to get it for those that already progressed to higher levels and then combine it all.

It would be a lot easier to describe your requirement had you provided some sample input data and explained the logic based on this data set. — Giorgos Betsos
– Giorgos Betsos, Commented Feb 10, 2018 at 7:30
Shouldn't the date diff be on the min() and max() of the dates at level 3? For all users that have a record at level 3? — Sloan Thrasher
– Sloan Thrasher, Commented Feb 10, 2018 at 7:41
Edit your question, and add some sample data rows, and the expected output from those rows. — Sloan Thrasher
– Sloan Thrasher, Commented Feb 10, 2018 at 7:42

Miljen Mikic · Accepted Answer · 2018-02-10 07:55:48Z

1

select g.gameid as gid, datediff(curdate(), l.dateofchange) as datediff
from gamehistorylog l
join games g on g.gameid = l.gameid where l.level = 3 and g.status = 3
UNION
select a.gameid as gid, datediff(a.dateofchange, b.dateofchange) as datediff
from gamehistorylog a
join gamehistorylog b on a.gameid = b.gameid
where a.level = 4 and b.level = 3

edited Feb 10, 2018 at 7:55

answered Feb 10, 2018 at 7:48

Miljen Mikic

15.3k8 gold badges64 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySql - Query and combine different conditions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related