[MySQL] Separating two rows into separate columns
I'm working on a final project for my database class, and I'm stuck on a specific task in the project. Basically, I have two tables (person & message), and I need to combine certain aspects from the databases. The instructions are pictured here https://i.imgur.com/rFLsEQy.png . The code that I have so far is this.
(SELECT
person.first_name AS "Sender First Name",
person.last_name AS "Sender Last Name",
message.message_id,
message.message,
message.send_datetime AS "Message Timestamp"
FROM message
JOIN person ON person.person_id = message.sender_id
WHERE message.sender_id = 1)
UNION
(SELECT
person.first_name AS "Receiver First Name",
person.last_name AS "Receiver Last Name",
message.message_id,
message.message,
message.send_datetime AS "Message Timestamp"
FROM message
JOIN person ON person.person_id = message.receiver_id
WHERE message.sender_id = 1);
& the output is https://i.imgur.com/oDc0T3s.png . I've been trying to research this for a few days and can't figure out how to separate the Receiver First and Receiver Last Name into separate columns. Any help would be appreciated, thank you.
Posted Dec 16, 2018, 11:42 PM UTC
910