CODEWITHSUNDEEP
phpmysqlleft-join
Ben
Ben

Reputation: 1914

Left join one table with itself

SELECT s1.receiverID, s2.senderID
FROM messages as s1
LEFT JOIN messages as s2 ON s1.receiverID = s2.senderID

I want to combine "messages" table where the senderID and receiverID are the same to achieve something like conversation flow, but when i run this it returns 10,422 total rows.When the all rows is 3000 so how it returns more ?!

// Sample scenario: user with id = 8 goes to this url sample.com/inbox/user=275. And i want to show all messages between them to achieve something like conversation flow/chat.

Upvotes: 2

Views: 387

Answers (3)

Dave Rael
Dave Rael

Reputation: 1759

you don't want a join. you just want to get all the messages that have both users as sender and receiver.

select * -- (or whatever)
from messages
where
(senderid = [userId] and receiverid = [otherUserId])
or (senderid = [otherUserId] and receiverid = [userId])

the join as you have written it is saying "give me every combination of messages in the system where the sender of one message is the same as the receiver of another and if there is anyone that has received a message but not sent any, show me those too with nulls for the values for sender."

Upvotes: 2

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Maybe this is what you're actually looking for?

select t1.senderID as t1senderID, t1.receiverID as t1receiverID,
  t2.senderID as t2senderID, t2.receiverID as t2receiverID
from messages t1
join messages t2
on t1.senderID = t2.receiverID and t1.receiverID = t2.senderID

Sample date would help a lot.

Edit:

Given this comment:

We have user A and user B and i want to return all messages between them

You're looking for this:

select * from t1
where (senderID = 'A' and receiverID = 'B') or
      (senderID = 'B' and receiverID = 'A')

Upvotes: 1

user1137032
user1137032

Reputation:

This example could help you figure out why you get more rows after the join.

rID sID
1   2
1   3
2   1
3   1

s1.rID s1.sID s2.rID s2.sID
1      2      2      1
1      2      3      1
1      3      2      1
1      3      3      1
2      1      1      2
3      1      1      3

Upvotes: 0

Related Questions