· 8 years ago · Apr 12, 2018, 06:28 AM
1
2DELIMITER //
3CREATE PROCEDURE contributors_for_department
4(IN con CHAR(20))
5BEGIN
6CREATE TEMPORARY TABLE IF NOT EXISTS temp_idea_by_comment as
7SELECT
8 COUNT(Comment_Id) as count,
9 i.Idea_Id
10FROM `ideas` i
11LEFT JOIN comments c ON c.Idea_Id = i.Idea_Id
12GROUP BY i.Idea_Id;
13
14SELECT i.* FROM temp_idea_by_comment tmp_i
15INNER JOIN ideas i ON i.Idea_Id = tmp_i.Idea_Id
16WHERE tmp_i.count = 0;
17
18END//
19DELIMITER //