-- select all members of AM 2 Battalion (taw id 1330)
select p.* from People p
join PeopleToUnits p2u on p2u.PersonId = p.PersonId and p2u.UnitId in
(
	select * from
		(select battalion.UnitId from Units battalion where battalion.TawId = '1330') a
	union all
        (select platoon.UnitId from Units battalion
		join Units platoon on battalion.UnitId = platoon.ParentUnit_UnitId and battalion.TawId = '1330')
	union all
		(select squad.UnitId from Units battalion
		join Units platoon on battalion.UnitId = platoon.ParentUnit_UnitId and battalion.TawId = '1330'
		join Units squad on platoon.UnitId = squad.ParentUnit_UnitId)
	union all
		(select fireteam.UnitId from Units battalion
		join Units platoon on battalion.UnitId = platoon.ParentUnit_UnitId and battalion.TawId = '1330'
		join Units squad on platoon.UnitId = squad.ParentUnit_UnitId
		join Units fireteam on squad.UnitId = fireteam.ParentUnit_UnitId)
)
group by p.PersonId
order by name;