title: How to select multiple columns from a different table using two different ids?
body: <p>For a project, I have to find all messages a specific person has sent. So, say I have these tables:</p>
  
  <p><strong>person</strong></p>
  
  <pre><code>person_id | first_name | last_name 
  </code></pre>
  
  <p><strong>message</strong></p>
  
  <pre><code>message_id | sender_id | receiver_id | message
  </code></pre>
  
  <p>Both sender_id and receiver_id reference person_id. I have to display sender's first and last name, reciever's first and last name, and message. I'm specifically having trouble displaying sender's names and receivers's names.</p>
  
  <p>I've tried a variety of searches for my problem but nothing seems to work or really relate to my problem. I've seen some solutions use UNION but I have not learned that in my class yet. This is what I have so far:</p>
  
  <pre><code>SELECT p.first_name AS "Sender First Name",
      p.last_name AS "Sender Last Name",
      p.first_name AS "Reciever First Name",
      p.last_name AS "Reciever Last Name",
      m.message_id AS "Message ID",
      m.message AS "Message",
      m.send_datetime AS "Message Timestamp"
  FROM message m, person p
  WHERE m.sender_id = 1
  AND m.sender_id = p.person_id;
  </code></pre>
  
  <p>That gives me:</p>
  
  <pre><code>+-------------------+------------------+---------------------+--------------------+------------+--------------------------------------------+---------------------+
  | Sender First Name | Sender Last Name | Reciever First Name | Reciever Last Name | Message ID | Message                                    | Message Timestamp   |
  +-------------------+------------------+---------------------+--------------------+------------+--------------------------------------------+---------------------+
  | Michael           | Phelps           | Michael             | Phelps             |          1 | Congrats on winning the 800m Freestyle!    | 2016-12-25 09:00:00 |
  | Michael           | Phelps           | Michael             | Phelps             |          4 | Thanks!  You're the greatest sprinter ever | 2016-12-25 09:04:00 |
  | Michael           | Phelps           | Michael             | Phelps             |          5 | Good luck on your race                     | 2016-12-25 09:05:00 |
  +-------------------+------------------+---------------------+--------------------+------------+--------------------------------------------+---------------------+
  </code></pre>
  
  <p>So sender's names are correct but receiver's are not. If I change the last line to</p>
  
  <pre><code>AND m.receiver_id = p.person_id;
  </code></pre>
  
  <p>then I get the correct receiever names but not the correct sender names.</p>