1DROP VIEW thread_view; 2 3CREATE VIEW thread_view 4AS 5SELECT thread.id, thread.original_poster, thread.coordinator, thread.closed_by, 6 thread.private, thread.lock, thread.type, 7 thread.create_date, thread.last_activity_date, thread.last_modification_date, thread.close_date, 8 thread.title, thread.content, thread.content_html, 9 thread.task_count, thread.task_count_closed, thread.task_sum_cost, 10 label.id AS label_id, 11 label.name AS label_name, 12 (SELECT MAX(priority) FROM task_view WHERE task_view.thread_id = thread.id) AS priority, 13 CASE WHEN thread.state = 'proposal' THEN 0 14 WHEN thread.state = 'opened' AND thread.task_count = 0 THEN 1 15 WHEN thread.state = 'opened' THEN 2 16 WHEN thread.state = 'closed' THEN 3 17 WHEN thread.state = 'rejected' THEN 4 END AS sort, 18 CASE WHEN thread.state = 'opened' AND thread.task_count > 0 AND thread.task_count = thread.task_count_closed THEN 'done' 19 ELSE thread.state END AS state, 20 (SELECT COUNT(*) FROM task WHERE type = 'corrective' AND thread_id=thread.id) AS corrective_count, 21 (SELECT COUNT(*) FROM task WHERE type = 'preventive' AND thread_id=thread.id) AS preventive_count 22FROM thread 23 LEFT JOIN thread_label ON thread.id = thread_label.thread_id 24 LEFT JOIN label ON label.id = thread_label.label_id;