Transact SQL Hierarchical Queries

Require resource to build Hierarchical Transact-SQL Queries Table 1 Geoboundary: Name (city, state, sales territory) Table 2 GeoboundaryType: Name (categorises geoboundary state, city etc) Table 3 GeoboundaryAssociation: FromGeoboundary - ToGeoboundary Table 4 GeoboundaryAssociationType (describes association- city to state) Table3 contains geographic boundary pairs (to and from) i.e. postcode to suburb suburb to city city to state Need to return all elements of any hierarchy as a result to be used in a form: for example user enter a postcode: -postcode is passed as the query input -result postcode, suburb(s), city, state -suburb is passed as the query input -result postcode, suburb(s), city, state Although the examples are around zipcodes, suburbs, cities, etc table1 stores any geographic boundary as (Sales Territory, Government Region) A GeographicBoundary may or may not have a relationship defined in table 3 Would also like to determine whether to restructure to use hierachyid in tansact-sql we are using SQL Azure