inner join nesting Sql script

Hi,
I would like to concatinate the following three scripts in to one script.
What I need is to have for each name and address returned from the frirst script all the members associated to that member from the second script and all the charges associated the the name from the first script.
What I think I have to do is multipul inner joins but this is above my knowelege of SQL.
here are the scripts
Script 1
===============================
Select
      t.hmyperson
      ,t.sfirstname + ' ' +t.slastname Tname
      ,t.saddr1
      ,t.scity
      ,t.sstate
      ,t.szipcode

FROM   tenant t

ORDER by t.hmyperson
=========================================
Script 2
=========================================
Select  
      T.hMyPerson
      ,ltrim(IsNull(F.sFIrstName + ' ', '') + IsNull(F.sLastName, '')) MemberName
From       property p
      inner join tenant t on (p.hmy = t.hproperty)
      Inner Join H8Family F on F.hTenant = T.hMyPerson
      And F.hSum = dbo.fh8_PRH_GetLatest58(T.hMyPerson, '07/01/08', '123456789ABCDEF')
Where
      F.iAge > 18
      AND F.sRelation <> 'H'
      
order by t.hmyperson
=====================================
Script 3
=====================================
Select
      t.hmyperson
      ,convert(varchar,tr.sDateOccurred,101) as dateocc
      ,'RENT' chargetype
      ,tr.sTotalAmount as Charge
      , tr.samountpaid as paid
      ,sum(tr.sTotalAmount - tr.samountpaid)unpaid

FROM   Acct a, Trans tr
                     left outer join acct ar on tr.hAccrualAcct = ar.hMy
                     left outer join chargtyp ct on tr.hRetentionAcct = ct.hMy, tenant t, tenstatus ts, property p
                  

WHERE  
      tr.iType = 7
         AND tr.hOffsetAcct = a.hMy
         and tr.hPerson = t.hMyPerson
         AND tr.hProp = p.hMy
         and t.iStatus = ts.istatus
         and tr.bopen=-1
         and ts.status = 'current'

group by t.hmyperson, t.scode, t.slastname,t.sfirstname,tr.hmy,t.sunitcode,
    ts.status,tr.sdateoccurred,tr.upostdate,ct.scode,a.scode,ar.scode,tr.stotalamount,tr.snotes,p.scode,t.sunitcode,t.putcode,tr.samountpaid,t.saddr1,t.scity,t.sstate, t.szipcode
Having
      sum(tr.sTotalAmount) >= '0'
ORDER by t.hmyperson, tr.sDateOccurred, p.scode

Thank you for any help you can give me
Al


      Start Free Trial