MySQL Query Help for Multiple Tables

Alright so I’m working on a final assignment and I’m super stuck on this one task. I’ve tried looking it up and reading guides and I got nowhere. To make matters worse, my professor hasn’t bothered to teach us how to do this particular type of task and I can’t bother asking him for help since it takes him days to respond. The task is,

“Find All of the Messages that Michael Phelps Sent.

Construct the SQL statement to find all of the messages that Michael Phelps 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

I have 5 tables in my database (contact_list, image, message_image, person, and message).

Generally this would be fairly easy, however, I’m stuck on how to get the sender’s and receiver’s names. I have to somehow cross reference the sender_id and receiver_id from the message table with the person_id from the person table. That is what I’m stuck on.

How the heck do I accomplish this? I know how to set it up so it will give the id numbers but I have no clue how to I guess convert it? So it shows their names instead? I considered just making another table and then querying from that instead but I’m fairly certain I’ll get marked off for that.

Any help would be greatly appreciated.

Data from person table:

person_id| first_name| last_name|location

1|        Michael| Phelps| Maryland, USA

2|        Katie| Ledecky| Maryland, USA

3|        Usain| Bolt| Kingston, Jamaica

4|        Allyson| Felix| California, USA

5|        Kevin| Durant| New York City, USA

7|        Elijah| Steger| Oklahoma, USA

Data from message table:

| message_id | sender_id | receiver_id | message | send_datetime|

|          1 |         1 |           2 | Congrats on winning the 800m Freestyle!    | 2016-12-25 09:00:00 |

|          2 |         2 |           1 | Congrats on winning 23 gold medals!        | 2016-12-25 09:01:00 |

|          3 |         3 |           1 | You're the greatest swimmer ever           | 2016-12-25 09:02:00 |

|          4 |         1 |           3 | Thanks!  You're the greatest sprinter ever | 2016-12-25 09:04:00 |

|          5 |         1 |           4 | Good luck on your race                     | 2016-12-25 09:05:00 |

Expected Result: The table would be too large to show but essentially I need to show every single message Michael Phelps sent and the table needs to show his first and last name, the recipient’s first and last name, the message id, the message itself, and the timestamp for when the message was sent.

My initial attempt was along the lines of;

SELECT 
s.first_name AS "Sender's First Name",
s.last_name AS "Sender's Last Name",
r.first_name AS "Receiver's First Name",
r.last_name AS "Receiver's Last Name",
m.message_id AS "Message ID",
m.message AS "Message",
m.send_datetime AS "Message Timestamp",
FROM
person s,
person r,
message m,
WHERE
m.sender_id = 1 AND
s.person_id = m.sender_id AND
r.person_id = m.receiver_id;

Unfortunately, it just doesn’t work.

Answer

I hope you haven’t learnt this join syntax in class. Comma-separated joins were used in the 1980s and made redundant in Standard SQL 1992.

Your error is the comma here:

message m,

and here:

m.send_datetime AS "Message Timestamp",

This is how your query should be written:

SELECT 
  s.first_name AS "Sender's First Name",
  s.last_name AS "Sender's Last Name",
  r.first_name AS "Receiver's First Name",
  r.last_name AS "Receiver's Last Name",
  m.message_id AS "Message ID",
  m.message AS "Message",
  m.send_datetime AS "Message Timestamp"
FROM message m
JOIN person s ON s.person_id = m.sender_id
JOIN person r ON r.person_id = m.receiver_id
WHERE m.sender_id = 1
ORDER BY m.send_datetime;

Advertisement

Answer