SQL result set containing results from 2 differnet rows

[chili5]

I have a database table with four fields:

ref_id
ref_refid
ref_amount
ref_date

The two id numbers is what I'm playing around with. These two numbers link to a customers table to find out who is making a referral and who was referred. What I want to do is select the phone numbers for both of these customers which are stored in these fields:

homeArea
homePrefix
homeLine

However, the problem I am running into is I can only select each column once when matching custId in the customers table with one of the ids. Is what I am wanting to do even possible or would I have to use two separate queries to do it? I can't use a join because it can't match the cust id twice on a row. I'm having a hard time describing my problem for a title though.

Now say ref_id is 13 and ref_refid is 15.

The result set should be:

refPhone | custPhone
555-555-5555 | 323-456-3233

Any thoughts?

[Orjan]

untested, but I believe you could join twice...
something like this maybe?

select a.id, a.phone, b.id, b.phone from ref r join customer a on ref.id=a.id join customer b on ref.refid=b.id

[chili5]

I think your right to use two joins. I think I got it.

Thanks orjan. That is kind of confusing though. Thanks a bunch!

[chili5]

Actually I just thought of something (stupid me over complicating things again) if customer x refers some customer y, and I want to display all the referrals that customer x has made, I don't need to select the information for customer x for every referral. It is always going to be the same, so I don't even need to do that.

So I query the database once for the customer making the referrals phone number and then query for the appropriate phone numbers for the customers that have been referred.

The only reason my query was confusing is because it is confusing. I was trying to do something that absolutely makes no sense.

[Orjan]

It could make sense without a problem to me, if you might wanna do a list so you order by or group by a either id or refid.