Inside a table I've the next value:
ID | Exercise1 | Exercise2 | Exercise3 1 | 0 | 0 | 0 2 | 0 | 0 | 0
Whenever a user completes a workout, the db switches from '0' to '1'. I am searching for an sql query that searches through the ID quantity of the consumer returns the cheapest column title that's set to .
ID | Exercise1 | Exercise2 | Exercise3 1 | 1 | 1 | 0
Here the query would return with
exercise2 have formerly been up-to-date and performed by the consumer.
SELECT COLUMN_NAME FROM information_schema.columns
but can't assemble it using the sorting I am searching for, any help could be deeply appreciated.
For those who have only a number of exercises (e.g. < 5), you'll be able to simply hardcode the query with a number of nested IF() claims.
For those who have in addition to that, then you definitely should improve your data model so each user/exercise mapping is saved inside a separate row.
Something similar to this?
SELECT CASE WHEN Exercise1=0 THEN 'Exercise1' WHEN Exercise2=0 THEN 'Exercise2' WHEN Exercise3=0 THEN 'Exercise3' ELSE NULL END AS Exercise FROM MyTable WHERE ID = SomeID
Err... you've problems since your design is wrong. However , your database design was impacted by the way you think of the presentation on the table. However the database thinking differs. The database could be normally designed by doing this:
StudentID | ExcerciseID | Completed 1 | 1 | 1 1 | 2 | 1 1 | 3 | 0 2 | 1 | 0 ....
And you can perform:
select StudentID, min(ExcerciseID) as FirstExcerciseNotCompleted from Excercises where Completed = 0
to determine first incomplete excercise for every student, or if you would like set next completed excercise to Student 1, simply do:
update Excercises set Completed = 1 where Student = 1 and ExcerciseID = (select min(ExcerciseID) from Excercises where StudentID = 1 and Completed = 0)