I have the following sql statement
SELECT a.Unitid, rtrim(a.resifirstname) + space(1) + rtrim(a.resilastname) as name, a.sec1onhand as secd,a.sec2onhand as secp,a.resiID,a.bldgid,a.moveindate,
a.creditstatus, b.timeslate,a.resifirstname, a.resilastname,a.leaseenddate,a.mtmflag,EMail,a.PropertyId,(select top 1 phone1no from occupantheader where propertyid = a.propertyid and ResiId = a.ResiId and BldgId=a.bldgid and UnitId=a.unitid order by occuseqno) as phone1no,a.leasebegdate,
(select UnitType from unit where propertyid = a.propertyid and BldgId=a.bldgid and UnitId=a.unitid) as unittype, (select Top 1 addrid from occupantheader where propertyid = a.propertyid and BldgId=a.bldgid and UnitId=a.unitid and resiid = a.resiid order by Occuseqno) as eMail2
FROM Lease a
inner join LeaseRecLNotice b on a.propertyid = b.propertyid and a.bldgid = b.bldgid and a.unitid = b.unitid and a.resiid = b.resiid
inner join addressbook c on a.primaryaddrid = c.addrid
WHERE (a.LeaseEndDate >= '03/01/2015') AND (a.LeaseEndDate <= '03/31/2015') AND a.PropertyId IN (SELECT code from locationList where LocationID='18') and (a.ResiStatus = 'C')
ORDER BY a.propertyid,UnitId
How can I join the email2 field to the addressbook.addrid so that I can receive the data in the email field of the record.
when I do a join like inner join addressbook d on d.addrid = email2 I receive an error.
Any Ideas on how I can do this.