CODEWITHSUNDEEP
mysqljoin
user1135798
user1135798

Reputation: 23

MySQL join on the same field

I have two tables:

  1. messages - with following structure

    id
    sender_id
    receiver_id
    message_text

  2. users - with following structure

    id
    nickname

I need to join these two tables so I get result like this:

message_id, sender_nickname, receiver_nickname, message_text

sender_id is always set. The problem is I also need messages where receiver_id is 0 so receiver_id can't be found in user table.

Could someone help me?

Upvotes: 2

Views: 171

Answers (5)

Lokesh Gupta
Lokesh Gupta

Reputation: 304

You can use outerjoin in it

select 
m.id as message_id, us.nickname as sender_nickname, ur.nickname as receiver_nickname, m.message_text as message_text
from messages m join users us on us.id = m.sender_id
left outer join users ur.id = m.receiver_id
where m.id = <your message id>

Upvotes: 0

Martin Dandanell
Martin Dandanell

Reputation: 851

SELECT ...
FROM messages
INNER JOIN users AS senders ON senders.id = messages.sender_id
LEFT JOIN users AS receivers ON receivers.id = messages.receiver_id

And keep the receiver_id column NULL instead of an INT value of 0, when there's no data.

Upvotes: 1

Mithrandir
Mithrandir

Reputation: 25397

You should use a left outer join when joining the messages table to the user table on the receiver_id.

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658887

Could look like this:

SELECT m.id AS message_id
      ,u1.nickname AS sender_nickname
      ,u2.nickname AS receiver_nickname
      ,m.message_text
FROM   messages m
LEFT   JOIN users u1 ON u1.id = m.sender_id
LEFT   JOIN users u2 ON u2.id = m.receiver_id

A LEFT [OUTER] JOIN guarantees that the left hand table will not be excluded if the right hand table has no matching value.

Upvotes: 1

Nicola Cossu
Nicola Cossu

Reputation: 56397

select 
m.id,
u1.nickname as sender,
u2.nickname as receiver,
m.message_text
from messages as m
left join users as u1 on u1.id = m.sender_id
left join users as u2 on u2.id = m.receiver_id

Upvotes: 2

Related Questions