Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 140
  • Last Modified:

How to use created field in sql to join another table

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

Open in new window

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.

1 Solution
Scott PletcherSenior DBACommented:
You don't say what the error is, but my guess is that you need to join on a different column in addressbook:

left outer join addressbook d on d.[email_address] = email2

Obviously "email_address" could be the wrong column name -- you need to check the addressbook to get the correct column name.  The point is that it is likely not addrid :-).


Moved your query into an inline table


Changed sort-order against occupantheader to descending, thinking you want the latest person occupying the unit (not the first)
FROM	(	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
		,	a.PropertyId
		,	(	SELECT top 1 phone1no
				FROM	occupantheader
				WHERE	propertyid = a.propertyid
				AND	ResiId = a.ResiId
				AND	BldgId = a.bldgid
				AND	UnitId = a.unitid
				BY	occuseqno DESC
			) 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
				BY	Occuseqno DESC
			) as eMail2
		FROM	Lease		a 
		JOIN	LeaseRecLNotice	b	ON	a.propertyid = b.propertyid
						AND	a.bldgid = b.bldgid
						AND	a.unitid = b.unitid
						AND	a.resiid = b.resiid
		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'
	)		x
JOIN	addressbook	y	ON	x.eMail2 = y.addrid 
BY	x.propertyid
,	x.UnitId

Open in new window

mgmhicksAuthor Commented:
That worked beautifully, and I did figure out how to get just the fields I need.  

thank you

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now