CODEWITHSUNDEEP
mysqlsqlwhere-clause
PFK B
PFK B

Reputation: 11

How can I output a column twice and with different values based on another table's reference?

I have two tables message and person:

  1. message table contains columns receiverID and senderID
  2. person table contains columns name and personID

personID and receiverID/senderID can be used to connect the tables

I'm having trouble outputting the name column twice, once based on the receiverID and the second based on senderID using the WHERE clause. I am only able to output name once based on personID = senderID.

SELECT person.first_name AS senderFirstName,     
(SELECT person.first_name FROM person, message WHERE person_id = receiver_id)    
FROM person, message   WHERE person_id = sender_id AND sender_id = 1;

This produces an ERROR 1242 (21000): Subquery returns more than 1 row. I just have no idea what to try next...very new to SQL.

Upvotes: 0

Views: 48

Answers (2)

forpas
forpas

Reputation: 164064

You need to join the table person twice to the table message:

select s.name, ...
from message m
inner join person s on m.senderid = s.personid
inner join person r on m.receiverid = r.personid
where m.senderid = 1

Upvotes: 2

Nikhil
Nikhil

Reputation: 3950

I guess you need this:

SELECT 
person.first_name AS senderFirstName, 
(SELECT p2.first_name FROM person p2 WHERE p2.person_id = 
m.receiver_id)receiveFirstName
FROM person p , message m 
WHERE p.person_id = m.sender_id AND m.sender_id = 1;

Upvotes: 0

Related Questions