How to write a complex SQL query with the intersection of two tables?

Question

MySQL

There is a table of messages (let's say messages) with columns id, date, from, to and text (from and to - id of the sender and recipient). And also there is a table of friends (let's say friends) with columns from and to (again, the id of the sender of the request to friends and the recipient).
Let's say my current Id is 123.
1) Firstly, I need to select the number of messages for me and from me from the messages table for each user with whom I corresponded. That is, if there are 3 messages from 123 to 456 (for example) and 2 messages from 456 to 123 in the messages table, then in response I should get a table with 2 columns: the first will store the user id with which the user given to us corresponds (123) , and in the second column, the number of messages from this user and to this user - that is, for the case I cited - 456.5.
2) Secondly, I need to "intersect" this result with the friends table, namely, from the result obtained in paragraph (1), leave only those rows for which there are 2 symmetrical records in the friends table, namely, if as a result of paragraph (1) there is a row 456, 5, then this line must be left only if there are 2 records in the friends table: 123, 456 and 456, 123, that is, in this way we will get a table with 2 columns: the friend's id and the number of messages from him.
I would be very grateful if you explain how to make such a request.

Answer 1

Implemented like this:

Answer 2

AND OR

Answer 3

1. It is done as a nested query - we group by user ID, count the number of records using COUNT.
2. We connect the table friendswith the help INNER JOINtwice - first for messages from you to a friend, then for messages from a friend to you. If at least one of the two required rows does not appear in the table friends, they will not be included in the resulting selection.