MySQL Query Help for Multiple Tables

Question

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.

Please show us 1) sample data for all relevant tables 2) expected result 3) what you attempted so far — GMB
– GMB, Commented Oct 20, 2019 at 20:17
Please format the table so that it is readable. Please also outline what "doesn't work" mean. The query produces some result, but you need to say what you expect. — Mats Kindahl
– Mats Kindahl, Commented Oct 20, 2019 at 20:33
This is the error I get, "ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM person s, person r, message m, WHERE m.sender_id = 1 AND s.person_id = m.se' at line 9" — EliSteger
– EliSteger, Commented Oct 20, 2019 at 20:39

Thorsten Kettner · Accepted Answer · 2019-10-20 22:09:58Z

1

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;

edited Oct 20, 2019 at 22:09

answered Oct 20, 2019 at 20:41

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

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

2 Comments

EliSteger Over a year ago

Unfortunately we have, along with a lot of other things I've been finding out are incorrect or no longer used. I think this is the first time I seriously regretted my choice in schools. I'll try out your suggestion.

EliSteger Over a year ago

Your suggestion worked perfectly, thank you so much.

Collectives™ on Stack Overflow

MySQL Query Help for Multiple Tables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related