How to join the same table twice?

I am stuck on creating a SQL query. I have the following two tables:

member
(
 memberid    int,
 membername  varchar(50)
)

message
(
 messageid   int, 
 message     varchar(100), 
 sender      int, 
 receiver    int
)

​x
 
member( memberid    int, membername  varchar(50))​message( messageid   int,  message     varchar(100),  sender      int,  receiver    int)​

sender and receiver are foreign keys with table member and column memberid. I want to display list of messages with name and id of sender and receiver.

How can I establish proper joins here?

Answer

You can try something like

SELECT  *
FROM    message m INNER JOIN
        member snd ON   m.sender = snd.memberid INNER JOIN
        member rcv ON   m.receiver = rcv.memberid

 
SELECT  *FROM    message m INNER JOIN        member snd ON   m.sender = snd.memberid INNER JOIN        member rcv ON   m.receiver = rcv.memberid​

Advertisement

Answer