Parent & Child View 1

[Mdavis123]

Have a simple request to do a mail list.
Join the Client with the Client Address Table
No Problemo.
Almost.
What if the are more than one address for a client?
Say that the Client address table stores the addresses and has a change_date field.
I would want the top/bottom record sorted by change_date [Asc/desc] to pick of the most recent/current address.

Do I use a view with 5 Select subqueries to get the address info or Do I write a sp?

Thanks MikeD

Here is the simple join
select c.fullname,
ca.Address as adr1,ca.Address1 as Adr2,
ca.CITY,ca.state,ca.zip

from client c,client_address ca
where c.clientkey=ca.clientkey

 

[tlbroadbent]

You should be able to accomplish this with one sub-query.

Select
c.fullname,
ca.Address as adr1,
ca.Address1 as Adr2,
ca.CITY,
ca.state,
ca.zip

From client c Inner Join client_address ca
On c.clientkey=ca.clientkey

Where ca.change_date =
(Select max(change_date)
From client_address
Where clientkey=c.clientkey) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[miked123]

Terry,
Thank You. I have been all around the solution but couldn't score. I couldn't believe an eloquent solution was so hard to come by.

Thanks Again,
Mike Davis
Going back to the drawing board on self joins and filters. Mike Davis
MSsql, VB and Crystal Reports Developer