need advise/help with my query.. 2

[DanDaBeginner]

I posted a while ago and though that my problem was solve..

 

I have this:

table name: message

 

Fields: message_id  |  recepient_id  |  sender_id  |  content  |  time_sent  |  read

        auto+increment          1                    2                hello!     

                                        2                    1                hi!         

                                        1                    3                kiss me!

                                        100                  1                oopss!

                                        1                    200              yikes...

--------------------------------------------------------------------------------------

now my problem is how can I query to get the distinct

"recepient_id that has a sender_id=1 " and "sender_id that has a recepient_id=1"

-------

SELECT DISTINCT sender_id as sid FROM dating_message WHERE recepient_id = 1

UNION

SELECT DISTINCT recepient_id as sid FROM dating_message WHERE sender_id = 1

-------

I already try this it is effective but how can I ORDER it according to time_sent so its use less..

[DanDaBeginner]

the output should look like this ORDER BY time_sent:

 

Message: Hello user 1:

 

conversation with 2: 'display the latest message send by bth of you'

 

conversation with 3: 'kiss me!'

 

conversation with 100: 'oopsss'

 

conversation with 200: 'yikes!'

[monk.e.boy]

SELECT DISTINCT sender_id as sid,recepient_id as rid
FROM dating_message
WHERE sid=1 or rid=1
ORDER BY time_sent

 

I'd guess you want to join sender_id to the users table to get the names of send+recipient

 

monk.e.boy

[DanDaBeginner]

thanx for the help. but thats not what I want.. sorry if I can't explain it very clearly..

 

for example the user has an ID=1, when the user clicked on the message page of the site

this page will display all the conversation that the user has, displaying the latest message that he has.

for example if the user message to Id=3 'HEllo' and ID=1 reply 'HI!' so the latest message is 'HI' between the

conversation of ID=1 and ID =3 so the output will

be like this:

 

Conversation with 3: 'HI'

---

[DanDaBeginner]

kindly check this one:

-------------

SELECT MAX(time_sent) as latest_msg, recepient_id

FROM dating_message WHERE sender_id = 1

GROUP BY recepient_id

UNION

SELECT MAX(time_sent) as latest_msg, recepient_id

FROM dating_message WHERE sender_id = 1

GROUP BY recepient_id

ORDER BY latest_msg DESC

--------------

im not sure on this one is this reasonable enough?