Selecting with two references to same table

Question

Maybe I have a bad design but I am currently trying to get an ordering system for a small store up and running. As it is a gift order can have a sender and recipient to show where and from an order is going.
So we have table

Person
personid
name 
address

Order
Sender_personid
Receiver_personid
etc...

I am having trouble creating a sql statement to select the names and address of both people in an order. If this can be done any pointers would be great. If not any design pointers equally apreciated.

Answer 1

I think you want something like this:

SELECT
    SP.*,
    RP,*,
    O.*
FROM Order O
JOIN Person SP ON SP.PersonID = O.Sender_PersonID
JOIN Person RP ON RP.PersonID = O.Receiver_PersonID

Just join on the table twice.

Answer 2

Basically you just do the same as if you just wanted to get 1 person (say, only the receiver), but this time you join the table twice. Table aliases can help understanding

SELECT receiver.name, sender.name
FROM order o, person as receiver, person as sender
where o.sender_personid = receiver.personid 
and o.sender_personid = sender.personid