Message table needs to obtain the name of sender and receiver from members table

Databases
1

What is the best way to modify my query so that I also get the full name of the receiver in addition to the sender?

Thanks!

select u.uID
, concat (u.firstname, ’ ', u.lastName) as sender
, m.senderID
, m.receiverID
, m.senderMessage
from users u
inner join messages m
on u.uID = m.senderID
where u.uID = m.receiverID or u.uID = m.senderID;

2

Join the user table again (with another alias), this time on the receiverID

3

and removes that WHERE Clause

4

That didn’t work, unless I misunderstood what you were saying:

select u.uID
, concat (u.firstname, ’ ', u.lastName) as sender
, concat (x.firstname, ’ ', x.lastName) as receiver
, m.senderID
, m.senderMessage
, x.receiverID
from users u
inner join messages m
on u.uID = m.senderID
inner join users x
on u.uID = x.receiverID;

5

change the part in red to –

x.uID = m.receiverID

6

Ok I tried what you said Rudy and got this error, “Unknown column ‘x.receiverID’ in field list”.

Thoughts?

7

Yes. You didn’t try what Rudy said :wink:
The part in red is what you wrote, and is wrong. You must change that to the code he posted at the end. And there is no x.receiverID in that code.

8

Checking further it was because I had x.receiverID earlier in the select statement before the FROM. I took that out and it works. Thanks to both of you!

9

Very good