Select 2 addresses from the same table in 1 record. 1

[BiggerBrother]

I have a table called address, which is linked to a national postcode database. I also have two tables, correspondence, and contacts.

My issue is that I need to return two addresses from the same table into the same recordset. The links are:
corres.to = contact.id
corres.from = contact.id
contact.id = address.id
I need to return a table, with 5 fields for each address, called add1 to add5 and also add_postcode.

Any ideas would be fantastic.

Many thanks

BB

 

[r937]

select address_from.field1 as add1_from
     , address_from.field2 as add2_from
     , address_from.field3 as add3_from
     , address_from.field4 as add4_from
     , address_from.field5 as add5_from
     , address_to.field1 as add1_to
     , address_to.field2 as add2_to
     , address_to.field3 as add3_to
     , address_to.field4 as add4_to
     , address_to.field5 as add5_to
  from correspondence as corres
inner
  join contact as contact_from
    on corres.from = contact_from.id 
inner
  join address as address_from
    on contact_from.id = address_from.id
inner 
  join contact as contact_to
    on corres.to = contact_to.id
inner
  join address as address_to
    on contact_to.id = address_to.id

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[BiggerBrother]

I was so far from the mark!!!! Fantastic.

BB