Thanks but I went with a cron job and a cursor to traverse the records and populated a parent child set of columns to make reporting quick.

I was hoping to create a join with a subquery but I run into the problem that there are may records matching the trunk and I need only the record...