Using JOIN statement in MYSQL to display data from two different tables

Question

I'm trying to get this to display but every time I try it, it says "empty set" instead of showing me what I need. Here is an image of the database schema

Here is what is being asked of me:

Construct the SQL statement to find all of the messages that message.sender_id = 1 sent. Note: You must use the WHERE clause to set the conditions for this query. Display the following columns: - Sender's first name - Sender's last name - Receiver's first name - Receiver's last name - Message ID - Message - Message Timestamp

And this is the MYSQL code that I came up with, but like I said, it only returns and empty set.

SELECT
 person.first_name AS "Sender's first name",
 person.last_name AS "Sender's last name",
 person.first_name AS "Receiver's first name",
 person.last_name AS "Receiver's last name",
 message.message_id AS "Message ID",
 message.message AS "Message",
 message.send_datetime AS "Message Timestamp"
FROM message
 JOIN person ON message.sender_id = person.person_id AND message.receiver_id = person.person_id
WHERE message.sender_id = 1;

I can't figure out how to get the first and last name for the person table to display based on the sender and receiver id in the message table.

Any help would be great! Thank you!

@AndySavage has the solution below, but what your query is effectively doing is finding messages where both the sender and receiver are the person where person_id = 1 — jmoerdyk
– jmoerdyk, Commented Apr 20, 2017 at 20:28

AndySavage · Accepted Answer · 2017-04-20 20:21:58Z

1

You need to join on person twice. One join to get the sender information, and one the receiver. As you are using the same table twice, you will need to alias it in the query. Something like...

SELECT
  msg_sender.first_name AS "Sender's first name",
  msg_sender.last_name AS "Sender's last name",
  msg_receiver.first_name AS "Receiver's first name",
  msg_receiver.last_name AS "Receiver's last name",
  message.message_id AS "Message ID",
  message.message AS "Message",
  message.send_datetime AS "Message Timestamp"
FROM message
JOIN person AS msg_sender ON message.sender_id = msg_sender.person_id 
JOIN person AS msg_receiver ON message.receiver_id = msg_receiver.person_id 
WHERE message.sender_id = 1;

answered Apr 20, 2017 at 20:21

AndySavage

1,7691 gold badge21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Darshan Mehta · Accepted Answer · 2017-04-20 20:22:39Z

0

You need to join twice on Person, the following should work:

SELECT s.first_name, s.last_name, r.first_name, r.last_name, m.message_id, m.send_datetime, m.message
FROM person s JOIN message m ON s.person_id = m.sender_id
JOIN person r ON m.receiver_id = r.person_id
WHERE s.person_id = 1;

answered Apr 20, 2017 at 20:22

Darshan Mehta

31k14 gold badges75 silver badges111 bronze badges

Comments

Keeper of the Grove · Accepted Answer · 2017-04-20 20:42:33Z

The way you wrote that looks like your person_id form the person table has to be "1" - also you sender_id and receiver_id from your message table have to equal "1" this is a very slim set or just outright invalid.

Select 
 t2.first_name AS "Sender's first name",
 t2.last_name AS "Sender's last name",
 t3.first_name AS "Receiver's first name",
 t3.last_name AS "Receiver's last name",
 t1.message_id AS "Message ID",
 t1.message AS "Message",
 t1.send_datetime AS "Message Timestamp"
FROM
  message t1
JOIN (SELECT first_name, last_name, person_id FROM person) t2 
  ON message.sender_id = t2.person_id
JOIN (SELECT first_name, last_name, person_id FROM person) t3
  ON message.receiver_id = t3.person_id
WHERE
 message.sender_id = 1

I didn't run this but it should give you the idea of what to write.

Collectives™ on Stack Overflow

Using JOIN statement in MYSQL to display data from two different tables

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related