Query for Item Type Tree

Hello,

A staff member is attempting to create a rather complex query to construct for Athletics that would show Item Types across Registration and Housing. Wondering if anyone can recommend any examples in either PS Query or just plain SQL. He is looking to report on all of the item types under a node (all children). Suggestions for records to use to pull that info?

Thanks!