Как составить сложный SQL запрос с пересечением двух таблиц?
Есть таблица сообщений (допустим messages) со столбцами id, date, from, to и text (from и to - id отправителя и получателя). А также есть таблица друзей (допустим friends) со столбцами from и to (опять же id отправителя запроса в друзья и получателя).
Допустим мой текущий Id: 123.
1) Во первых мне нужно из таблицы messages выбрать количество сообщений для меня и от меня для каждого пользователя, с которым я переписывался. То есть если в таблице messages присутствует 3 сообщения от 123 для 456 (например) и 2 сообщения от 456 для 123, то в ответ я должен получить таблицу с 2 столбцами: в первом будет храниться id пользователя с которым переписывается данный нам пользователь (123), а во втором столбце количество сообщений от этого пользователя и к этому пользователю - то есть для приведенного мною случая - 456, 5.
2) Во вторых мне нужно "пересечь" данный результат с таблицей friends, а именно из результата полученного в пункте (1) оставить только те строки для которых в таблице friends имеются 2 симметричные записи, а именно если в результате пункта (1) есть строка 456, 5, то эту строку необходимо оставить, только если в таблице friends есть 2 записи: 123, 456 и 456, 123, то есть таким образом мы получим таблицу с 2 столбцами: id друга и количество сообщений от него.
Буду очень благодарен, если объясните как составить подобного рода запрос.
1. Делается вложенным запросом - группируем по ID пользователя, считаем количество записей с помощью COUNT.
2. Подключаем таблицу friends с помощью INNER JOIN два раза - сначала для сообщений от вас другу, потом для сообщений от другу вам. Если хотя бы одной из двух требуемых строк в таблице friends не окажется, в результирующую выборку они не попадут.
Первый пункт не совсем ясен, можно чуть подробнее? Я понимаю как например посчитать кол-во сообщений от каждого пользователя ко мне или от меня для каждого пользователя, но не понимаю как это сложить.
Средний
