CODEWITHSUNDEEP
phpmysqlsql
MD SHAHIDUL ISLAM
MD SHAHIDUL ISLAM

Reputation: 14523

MySQL query to get a list of user from conversation list

I have a mysql table chat like

+---------+-----------+-------------+----------------+
| chat_id | sender_id | receiver_id | msg            |
+---------+-----------+-------------+----------------+
|       1 |      1002 |        1001 | test           |
|       2 |      1001 |        1002 | test           |
|       3 |      1002 |        1001 | test           |
|       5 |      1001 |        1002 | asdf           |
|       6 |      1003 |        1001 | tesdf          |
|       9 |      1001 |        1003 | tasdfa a fasd  |
|      10 |      1001 |        1004 | dsf asdf a     |
|      11 |      1005 |        1001 | dsf asdf asdf  |
+---------+-----------+-------------+----------------+

There is conversation of user 1001 among users 1002,1003,1004,1005

I need a list of users(1002,1003,1004,1005) whom with user 1001 did conversation.

What will be the mysql query? please help me.

Upvotes: 0

Views: 2014

Answers (6)

Patrick Kariuki
Patrick Kariuki

Reputation: 1

Of course this could have come earlier, unfortunately I too have been struggling with the same question for 9 months. I hope this will unblock the Suez.

SELECT *,
CASE
WHEN sender_id = 1001
THEN receiver_id
ELSE sender_id 
END as user_id
FROM chats
WHERE chat_id IN (SELECT MAX(chat_id) 
FROM chats WHERE receiver_id= 1001 
OR sender_id = 1001
GROUP BY IF (sender_id=1001, receiver_id,sender_id) )
ORDER BY chat_id DESC

Upvotes: 0

Ujjwal Ojha
Ujjwal Ojha

Reputation: 1360

(select distinct receiver_id from table where sender_id = 1001)
union distinct
(select distinct sender_id from table where receiver_id = 1001);

Upvotes: 0

rohitr
rohitr

Reputation: 371

HI Please check following query

select distinct receiver_id from table where distinct sender_id = 1001

Upvotes: 0

Sanal K
Sanal K

Reputation: 733

Just try this

select  DISTINCT if(receiver_id='1001',sender_id,receiver_id) AS id
from    YourTable
where   (sender_id = 1001 OR receiver_id = 1001)

EDIT:

SELECT GROUP_CONCAT( DISTINCT (
if( receiver_id = '1001', sender_id, receiver_id ) )
) AS concat
FROM chat
WHERE (
sender_id =1001
OR receiver_id =1001
)

I think you are aware about GROUP_CONCAT size issues. You can refer this http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Upvotes: 3

John Woo
John Woo

Reputation: 263723

You can select all records which a specific user you want to search is in either a sender or receiver. After that test if the sender's id is equal to the userid you want to search and if it is equal return receiver's id otherwise return sender. The DISTINCT keyword is supplied to show only unique ID on the list of result.

SELECT  DISTINCT 
        CASE WHEN sender_id = 1001 
            THEN receiver_id 
            ELSE sender_id 
        END userID
FROM    tableName
WHERE   1001 IN (sender_id, receiver_id)

since you want to concatenate the row based on your comment

SELECT  GROUP_CONCAT(DISTINCT
                    CASE WHEN sender_id = 1001 
                        THEN receiver_id 
                        ELSE sender_id 
                    END) userID
FROM    tableName
WHERE   1001 IN (sender_id, receiver_id)

Upvotes: 5

Andomar
Andomar

Reputation: 238086

You could use a union to combine two queries where sender or receiver is 1001:

select  distinct sender_id
from    YourTable
where   receiver_id = 1001
union
select  distinct receiver_id
from    YourTable
where   sender_id = 1001

Upvotes: 1

Related Questions