Solved

How to use created field in sql to join another table

Posted on 2015-01-08
3
134 Views
Last Modified: 2015-01-08
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.

thanks
0
Comment
Question by:mgmhicks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40539128
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 :-).
0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 500 total points
ID: 40539139

1.

Moved your query into an inline table

2.

Changed sort-order against occupantheader to descending, thinking you want the latest person occupying the unit (not the first)
SELECT	*
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
				ORDER
				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
				ORDER
				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
LEFT
JOIN	addressbook	y	ON	x.eMail2 = y.addrid 
ORDER
BY	x.propertyid
,	x.UnitId

Open in new window

0
 

Author Closing Comment

by:mgmhicks
ID: 40539289
That worked beautifully, and I did figure out how to get just the fields I need.  

thank you
0

Featured Post

Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question