21 lines
663 B
SQL
Executable File
21 lines
663 B
SQL
Executable File
-- # $Id$
|
|
-- # $HeadURL$
|
|
-- # $Author$
|
|
|
|
-- View for showing exerciseState changes
|
|
DROP VIEW IF EXISTS exm.getExerciseStateChanges;
|
|
CREATE VIEW exm.getExerciseStateChanges AS
|
|
(
|
|
SELECT e.name AS ex_name, e.id AS exercise_id,
|
|
s1.id AS s1_id, es1.name || '-' || s1.exercise_state_id AS state_old,
|
|
s2.id AS s2_id, es2.name || '-' || s2.exercise_state_id AS state_new
|
|
FROM exm.exercise_state_change s1, exm.exercise_state_change s2,
|
|
exm.exercise_state es1, exm.exercise_state es2,
|
|
exm.exercise e
|
|
WHERE s2.id - 1 = s1.id
|
|
AND e.id = s1.exercise_id
|
|
AND es1.id = s1.exercise_state_id
|
|
AND es2.id = s2.exercise_state_id
|
|
);
|
|
|