gistfile1.sql

delimiter $$

CREATE DEFINER=`jridley_admin`@`localhost` PROCEDURE jrridley_circlenew.`GetPostList`()
BEGIN
drop temporary table if exists jrridley_circlenew.tmpPosts;
drop temporary table if exists jrridley_circlenew.pIDs;
create temporary table jrridley_circlenew.pIDs(post_id int);
create temporary table jrridley_circlenew.tmpPosts(post_id int, group_id int, parent_id int, root_id int, author varchar(100), title varchar(100), content varchar(255), create_date timestamp);
Insert into jrridley_circlenew.pIDs(post_id) select root_id from jrridley_circlenew.post where root_id > 0 group by root_id order by count(root_id) DESC, create_date;
Insert into jrridley_circlenew.tmpPosts(post_id, group_id, parent_id, root_id, author, title, content, create_date)
Select p.post_id, group_id, parent_id, root_id, concat(u.first_name, ' ' , u.last_name), p.title, content, create_date from jrridley_circlenew.post p inner join jrridley_circlenew.users u on p.user_id = u.user_id inner join jrridley_circlenew.pIDs pi on pi.post_id = p.post_id;
select post_id, group_id, parent_id, root_id, author, title, content, create_date from jrridley_circlenew.tmpPosts;
drop temporary table if exists jrridley_circlenew.tmpPosts;
drop temporary table if exists jrridley_circlenew.pIDs;
END$$