Join table to itself?

[fikse]

Is there a way to join a table to itself?

Example table columns:

memberID
fname
lname
parterID

each member has a partner, and the partnerID is another member's memberID...

I need one query to list off of the members fname and lname, and then their partners lname and fname...

thanks!

[fakename]

I'm not sure if my qeuery is correct, but I believe you need to perform a join similar to this:

select a.memberID, a.fname, a.lname, b.partnerID, b.fname, b.lname
from table a, table b
where a.partnerID = b.memberID

[MarkR]

Absolutely, yes. You can definitely join a table to itself. The largest number I've ever tried was about 300 (joining the same table 300 times in one query)

You have to use aliases for the tables (and/or columns) to avoid ambiguity however.

Fakename's example looks fine to me (although I probably wouldn't alias them a and b)

Mark