CODEWITHSUNDEEP
mysqlsql
pankaj
pankaj

Reputation: 8348

SQL with inner join and subquery

I have following query:

SELECT * FROM thread where sender = 135 or receiver = 135 order by updatedttm desc

The above query returns correct record which has a senderid and receiverid. The senderid and receiverid are both present in user table. I want to get names of both senderid and receiverid by joining user table to above query.

Upvotes: 0

Views: 57

Answers (2)

Nils B.
Nils B.

Reputation: 26

I would go with LEFT JOIN as it will display your thread even if the sender or the receiver gets deleted.

SELECT *,
    sender.username AS sender_name,
    receiver.username AS receiver_name
FROM thread AS t
LEFT JOIN users AS sender ON t.sender = sender.user_id
LEFT JOIN users AS receiver ON t.sender = receiver.user_id
WHERE sender = 135 OR receiver = 135 
ORDER BY 
updatedttm DESC

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can join the user table twice, once with the sender id and the receiver id and get the name. 

SELECT t.*,usend.user_name sender_name, urec.user_name receiver_name
FROM thread t
JOIN users usend on usend.user_id = t.sender
JOIN users urec on urec.user_id = t.receiver
where sender = 135 or receiver = 135
order by updatedttm desc

Upvotes: 5

Related Questions