DELIMITER //
CREATE PROCEDURE needed_skus(@parent_category_id int(1))
BEGIN
	TRUNCATE TABLE bng347_product_ids;

	CREATE TABLE @temp_cat_id(virtuemart_category_id int(1));
	INSERT INTO @temp_cat_id(virtuemart_category_id) SELECT @parent_category_id as virtuemart_category_id;

	FOR EACH @category_id IN @temp_cat_id 
	BEGIN
		INSERT @temp_cat_id(virtuemart_category_id)
		SELECT child_category_id as virtuemart_category_id
		FROM ytgb1_virtuemart_category_categories
		WHERE 
			category_parent_id = @category_id;
	END

	INSERT bng347_product_ids(virtuemart_product_id)
	SELECT 
		DISTINCT
		virtuemart_product_id
	FROM ytgb1_virtuemart_product_categories pc
	JOIN @temp_cat_id USING(virtuemart_category_id)
END//
DELIMITER ;

CREATE PROCEDURE needed_ids(parent_category_id int(1))
LANGUAGE SQL
SQL SECURITY DEFINER
BEGIN
	TRUNCATE TABLE bng347_product_ids;
	DROP TEMPORARY TABLE IF EXISTS temp_cat_id;
	CREATE TEMPORARY TABLE temp_cat_id(virtuemart_category_id int(1));
	INSERT INTO temp_cat_id(virtuemart_category_id) SELECT parent_category_id as virtuemart_category_id;

	FOR EACH @category_id IN temp_cat_id
	BEGIN
		INSERT temp_cat_id(virtuemart_category_id)
		SELECT child_category_id as virtuemart_category_id
		FROM ytgb1_virtuemart_category_categories
		WHERE 
			category_parent_id = @category_id;
	END

	INSERT bng347_product_ids(virtuemart_product_id)
	SELECT 
		DISTINCT
		virtuemart_product_id
	FROM ytgb1_virtuemart_product_categories pc
	JOIN temp_cat_id USING(virtuemart_category_id)
END//

DELIMITER //

CREATE PROCEDURE needed_ids(parent_category_id int(1))
LANGUAGE SQL
SQL SECURITY DEFINER
BEGIN
	TRUNCATE TABLE bng347_product_ids;

	DROP TEMPORARY TABLE IF EXISTS sought_cats;
	CREATE TEMPORARY TABLE sought_cats(id int(1) unsigned NOT NULL AUTO_INCREMENT, virtuemart_category_id int(1));
	INSERT INTO sought_cats(virtuemart_category_id) SELECT parent_category_id as virtuemart_category_id;

	DROP TEMPORARY TABLE IF EXISTS allcats;
	CREATE TEMPORARY TABLE allcats(virtuemart_category_id int(1));
	INSERT INTO allcats(virtuemart_category_id) SELECT parent_category_id as virtuemart_category_id;
	
	DECLARE is_there_anybody INT DEFAULT 1;
	DECLARE last_row INT;
	
	WHILE is_there_anybody > 0	DO
		SET last_row = (SELECT MAX(id) FROM sought_cats);

		INSERT allcats(virtuemart_category_id)
		SELECT child_category_id as virtuemart_category_id
		FROM ytgb1_virtuemart_category_categories cc
		JOIN sought_cats sc ON sc.virtuemart_category_id = cc.category_parent_id
		
		INSERT sought_cats(virtuemart_category_id)
		SELECT child_category_id as virtuemart_category_id
		FROM ytgb1_virtuemart_category_categories cc
		JOIN sought_cats sc ON sc.virtuemart_category_id = cc.category_parent_id

		DELETE FROM sought_cats WHERE id <= last_row;
		
		SET is_there_anybody = SELECT COUNT(*) FROM sought_cats
		
	END WHILE;

	INSERT bng347_product_ids(virtuemart_product_id)
	SELECT 
		DISTINCT
		virtuemart_product_id
	FROM ytgb1_virtuemart_product_categories pc
	JOIN allcats USING(virtuemart_category_id)
END//
DELIMITER ;

DROP PROCEDURE IF EXISTS needed_ids;
DELIMITER //

CREATE PROCEDURE needed_ids(parent_category_id int(1))
LANGUAGE SQL
SQL SECURITY DEFINER
BEGIN
	DECLARE is_there_anybody INT DEFAULT 1;
	DECLARE last_row INT;

	TRUNCATE TABLE bng347_product_ids;

	DROP TEMPORARY TABLE IF EXISTS sought_cats;
	CREATE TEMPORARY TABLE sought_cats(
		id int(1) unsigned NOT NULL AUTO_INCREMENT, 
		virtuemart_category_id int(1),
		PRIMARY KEY (`id`));
	INSERT INTO sought_cats(virtuemart_category_id) SELECT parent_category_id as virtuemart_category_id;

	DROP TEMPORARY TABLE IF EXISTS allcats;
	CREATE TEMPORARY TABLE allcats(
		id int(1) unsigned NOT NULL AUTO_INCREMENT, 
		virtuemart_category_id int(1),
		PRIMARY KEY (`id`));
	INSERT INTO allcats(virtuemart_category_id) SELECT parent_category_id as virtuemart_category_id;

	WHILE is_there_anybody > 0	DO
		/*SET last_row = (SELECT MAX(id) FROM sought_cats sc);*/
		SET last_row = (SELECT MAX(id) FROM allcats);
		INSERT allcats(virtuemart_category_id)
		SELECT category_child_id as virtuemart_category_id
		FROM ytgb1_virtuemart_category_categories cc
		JOIN sought_cats sc ON sc.virtuemart_category_id = cc.category_parent_id;
		
		INSERT sought_cats(virtuemart_category_id)
		SELECT category_child_id as virtuemart_category_id
		FROM ytgb1_virtuemart_category_categories cc
		JOIN allcats ac ON ac.virtuemart_category_id = cc.category_parent_id AND ac.id > last_row;

		DELETE sc FROM sought_cats sc WHERE id <= last_row;
		
		SET is_there_anybody = (SELECT COUNT(*) FROM sought_cats);
		
	END WHILE;

	INSERT bng347_product_ids(virtuemart_product_id)
	SELECT 
		DISTINCT
		virtuemart_product_id
	FROM ytgb1_virtuemart_product_categories pc
	JOIN allcats USING(virtuemart_category_id);
END//
DELIMITER ;