How can I have a conditional or multiple join? 1

[SharonFinLV]

I have two tables:

[b]PAYEES[/b]
  [i]OldID   NewID   PayeeName[/i]
  [b]10001   10001[/b]   Alan
  [b]20002[/b]   20001   Bob
  30003   [b]30001[/b]   Carl
  40004   40004   Doug

[b]MEMBERS[/b]
  [i]MembID   MembAcct[/i]
  [b]10001[/b]    Xxx
  [b]20002[/b]    Yyy
  [b]30001[/b]    Zzz
  40001    Vvv

I want these results:

  [i]OldID   NewID   PayeeName   MembID   MembAcct[/i]
  10001   10001   Alan        10001    Xxx
  20002   20001   Bob         20002    Yyy
  30003   30001   Carl        30001    Zzz

As you can see:
- OldID can be same or different than NewID.
- Member can match OldID or NewID, if different, or neither.

If I'm going at this with the wrong approach... please, any ideas will be welcome.

Thanks!
SharonFinLV

 

[PHV]

You may try something like this:
SELECT OldID, NewID, PayeeName, MembID, MembAcct
FROM PAYEES, MEMBERS
WHERE MembID In(OldID, NewID)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SharonFinLV]

Wow, PHV, I've never done a query like that with multiple tables and no join.

It looks like it's doing exactly what I wanted. Thank you so much - this is a huge lifesaver for several projects!!

-SharonFinLV

 

[lespaul]

For more on Joins this is a great article:

'Understanding SQL Joins'

Leslie