This question has already been answered:

I make a chat. The database has the following structure:

Сущность Chat id text_message id_user_from id_user_to

You must get a list of conversations (that is, all non-repeating entries at the same time id_user_from and id_user_to ). Is it possible to somehow do this using SQL ?

I apologize, the question may be stupid, but I am not particularly strong in SQL .

enter image description here

Here is the upper table. This is the complete list of messages in the dialogue. And the lower table is what I want to receive.

That is, all non-repetitive combinations id_user_from! And! id_user_to

Reported as a duplicate by members of Mike , aleksandr barakin , sanmai ,, user207618 Jun 26 '17 at 16:28 .

A similar question was asked earlier and an answer has already been received. If the answers provided are not exhaustive, please ask a new question .

  • Using SQL tools you can make a selection of any complexity. Especially if the task is clearly articulated. Regarding your question, unfortunately, you can only guess in general terms. Maybe you need something like this.stackoverflow.com/questions/676395 or maybe an ordinary distinct ... - Mike
  • Why is the result “Yes, hello”, and not “Hello, Marin,” what message should the text be as a result of? - Mike
  • @Mike, well, with the analogy with messengers. There is built a list of dialogues, regardless of whether I SEND the message or SEND the message to me (the last one is shown). - Alexey

2 answers 2

Use SELECT DISTINCT :

 SELECT DISTINCT id_user_from, id_user_to FROM Chat

Or so:

 SELECT id_user_from, id_user_to FROM Chat GROUP BY id_user_from, id_user_to
  • does not fit, you need to somehow distinct user_from and distinct user_to at the same time - Aleksey
  • Try the option with GROUP BY - Sv__t
  • @ Alexey, please give an example of input data in the question and what you need to get at the output, otherwise it is not clear what exactly you need - Mike
  • @Mike, it seems I found a solution in the link that you dropped, thanks - Alex

The solution was found:

  SELECT * FROM Chat WHERE id IN ( SELECT max(id) as id FROM (SELECT id, id_user_from, id_user_to FROM Chat WHERE id_user_from =17 UNION ALL SELECT id, id_user_to, id_user_from FROM Chat WHERE id_user_to =17) t GROUP BY id_user_from, id_user_to ) ORDER BY id DESC

The query is not easy, but displays even in the right sequence

  • This once again confirms that it is necessary to more clearly describe the task, in your question not a word is said that it is necessary to make a sample for a specific user, and not for any. And by the way in this case, for that link ru.stackoverflow.com/questions/658939 right your question. and the answer differs only in the fact that when you receive the last message you need to take not the date, but the lpad(id,20,'0') - Mike
  • @Mike, thanks for the tip anyway) - Alexei