February 1, 2007 at 4:38 pm
hi all
I need to create a view with old/new addresses
Here are my tables
Subperson Indid adr_id pubcode name accountnumber 1 2 1H ABC 1HABC123 2 3 2B Smith 2BSmith123
Ind_id,pubcode,accountnumber is a primary key
Address Adr_id Addressline2 Addressline2 city state zip 1 1010 abc av apt 10 old ny 10010 2 123 def av apt 2A new ny 10011 3 350 park av suite 30 nyc ny 10089 4 250 park av suite 20 nyc ny 10089
OldAddress Indid adr_id pubcode accountnumber 1 1 1H 1HABC123 2 4 2B 2BSmith123
How can i achive this resultset?
pubcode name addressline1 addressline2 city state zip indid accountnumber newaddressline1 newaddressline2 newcity newstate newzip 1H ABC 1010 abc av apt 10 old ny 10010 1 1HABC123 123 def av apt 2A new ny 10011
Thanks for your help
February 1, 2007 at 5:11 pm
This hasn't been tested, but should give you what you need.
SELECT
sp.pubcode
,sp.name
,a2.addressline1
,a2.addressline2
,a2.city
,a2.state
,a2.zip
,indid -- which indid do you want here a2 or a ?
,sp.accountnumber
,a.addressline1 as newaddressline1
,a.addressline2 as newaddressline2
,a.city as newcity
,a.state as newstate
,a.zip as newzip
FROM
Subperson sp
LEFT JOIN Address a ON sp.adr_id = a.Adr_id
LEFT JOIN OldAddress oa ON sp.Indid = oa.Indid
LEFT JOIN Address a2 ON oa.adr_id = a2.Adr_id
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgFebruary 2, 2007 at 9:03 am
Jason thanks for solving it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply