×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Select 2 addresses from the same table in 1 record.

Select 2 addresses from the same table in 1 record.

Select 2 addresses from the same table in 1 record.

(OP)
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

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

CODE

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)

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

(OP)
I was so far from the mark!!!! Fantastic.

BB

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close