CODEWITHSUNDEEP
sqlsql-server
BastanteCaro
BastanteCaro

Reputation: 1290

Selecting with two references to same table

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.

Upvotes: 3

Views: 1981

Answers (2)

rsbarro
rsbarro

Reputation: 27339

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.

Upvotes: 8

Konerak
Konerak

Reputation: 39763

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

Upvotes: 2

Related Questions