drop table if exists question;
drop table if exists answer;
create table question(Question text, score int);
create table answer(answer text, score int);

drop procedure if exists Searching_Answers_Question_g;

delimiter //
create procedure Searching_Answers_Question_g( in inpute char (200))

begin
declare done int default false;
declare done1 int default false;
declare a char(200);
declare b int;
declare c char(200);
declare d int;

declare cur1 cursor for (select body, score from (select post_id from (select * from (select post_id as parent_id from (select * from post_tags
where match(tag_name) against(inpute IN BOOLEAN MODE) ) as t
natural join post
where match(post.title) against(+inpute IN BOOLEAN MODE)
and match(post.body) against(inpute in natural language mode)
order by post.score desc)as N  natural join answer) as S natural join post
group by parent_id
having max(post.score)) as ok natural join post);

declare cur2 cursor for(select body, score from (select parent_id as post_id from (select * from (select post_id as parent_id from (select * from post_tags
where match(tag_name) against(inpute IN BOOLEAN MODE) ) as t
natural join post
where match(post.title) against(+inpute IN BOOLEAN MODE)
and match(post.body) against(inpute in natural language mode)
order by post.score desc)as N  natural join answer) as S natural join post
group by parent_id
having max(post.score)) as ok natural join post);

declare continue handler for not found set done = true;

open cur1;
    read_loop: loop
        fetch cur1 into a, b;
        insert into answer values (a,b);
        if done then
            leave read_loop;
        end if;
    end loop;
close cur1;

open cur2;
    read_loop: loop
        fetch cur2 into c, d;
        insert into question values (c,d);
        if done then
            leave read_loop;
        end if;
    end loop;
close cur2;

select * from question;
select * from answer;

drop table if exists question;
drop table if exists answer;

end;
//delimiter ;

-- call Searching_Answers_Question_g('python');
call Searching_Answers_Question_g('how to program in python');