# Run this interatively until the reported count in TaxParentChild is unchanged

# First, put all our taxonomys with themselves as 0-distance parents
REPLACE TaxParentChild (parentId,childId,nDistance)
	SELECT taxonomyId parentId, taxonomyId childId, 0 nDistance
	FROM Taxonomy;

CREATE TEMPORARY TABLE IF NOT EXISTS TaxParentChild2 (
	parentId  INT(10) unsigned NOT NULL,
	childId INT(10) unsigned NOT NULL,
	nDistance INT(10) unsigned NOT NULL,
	key(parentId),
	key(childId),
	UNIQUE combined(parentId,childId)
);

# Expand up from known taxonomys
INSERT TaxParentChild2 (parentId,childId,nDistance)
	SELECT TaxNode.parentId parentId, TaxParentChild.childId childId, TaxParentChild.nDistance+1 nDistance
	FROM TaxNode,TaxParentChild
	WHERE TaxNode.taxonomyId=TaxParentChild.parentId AND TaxNode.parentId <> TaxNode.taxonomyId;
REPLACE TaxParentChild SELECT * FROM TaxParentChild2;
DELETE FROM TaxParentChild2;

# Expand up from intermediate nodes
INSERT TaxParentChild2 (parentId,childId,nDistance) SELECT DISTINCT parentId parentId, parentId childId, 0 nDistance
	FROM TaxParentChild;
REPLACE TaxParentChild SELECT * FROM TaxParentChild2;

DROP TABLE TaxParentChild2;

SELECT COUNT(*) nTaxParentChild FROM TaxParentChild;


