Getting data from multiple rows using joins in phpMyAdmin

Question

On a chat app am making, A user can start a conversation with any other user. In order to have threaded sms view, a user clicks on one of these conversation listview to get all messages sent or received by the person they are chatting with. How do i modify the query to show the sender name if am the receiver and receiver name if am the sender?

Example table
Id sender receiver
1   1           2
2   1           3
3   4           1
4   5           1
5   8           6

QUERY

SELECT conversation.Id,
       conversation.Sender,
       conversation.Receiver,
       users.username as NAME
FROM conversation
INNER JOIN users
    ON conversation.Sender = users.id
where sender = 1 OR receiver = 1

RESULT:

Id sender receiver NAME
1   1           2            razen
2   1           3            razen
3   4           1            peter
4   5           1            john

How do i modify the query to give me the name of the sender if am the receiver and the name of the receiver if am the sender?

The listview should only show other peoples names not mine.. (Show sam and kim for id 2&3 in the table above)

Your join has a problem: `ON conversation.Sender = users.username` ... this makes no sense. How can `username` be a text name and also a number at the same time? — Tim Biegeleisen, Mar 07 '17 at 04:10
Not really mr tim..the sender field is int(holds sender id) sender id is fed to the users table to give their username..the query works — RAZEN, Mar 07 '17 at 04:14

Tim Biegeleisen · Accepted Answer · 2017-03-07T04:21:39.043

0

How can the username column of the users table be a name and a number at the same time? This aside, I think you can do a join involving a CASE expression to get the result you want:

SELECT t1.Id,
       t1.Sender,
       t1.Receiver,
       t2.username AS NAME
FROM conversation t1
INNER JOIN users t2
    ON t2.id = CASE WHEN t1.Sender = 1   THEN t1.Receiver
                    WHEN t1.Receiver = 1 THEN t1.Sender END
WHERE t1.Sender = 1 OR
      t1.Receiver = 1

edited Mar 07 '17 at 04:21

answered Mar 07 '17 at 04:15

Tim Biegeleisen

502,043
27
286
360

Unrecognized keyword near CASE and five more errors follows – RAZEN Mar 07 '17 at 04:33
The query want is almost identical to [this question](http://stackoverflow.com/questions/25453762/mysql-joins-with-case-statements). My guess is that you copied my answer wrongly. – Tim Biegeleisen Mar 07 '17 at 04:36
Oh it works..the editor gives error but the query runs fine..thqnk you mr tim biegeleisen..answer accepted – RAZEN Mar 07 '17 at 04:51

Getting data from multiple rows using joins in phpMyAdmin

1 Answers1