Запрос из двух таблиц - MySQL (220101)

Формулировка задачи:

Помогите составить такой запрос. Вывести order_id и offer у которых:

active = 1
и не было отправлений за последние 5 дней

Есть ещё нюанс: таблицы соединены по составному ключу (order_id + offer).

Таблица заказов - "orders":
+----------+----------+--------+----------+
| order_id | offer    | active | timezone |
+----------+----------+--------+----------+
|        6 | kopiya   |      1 |        0 |
|        6 | kopiya-3 |      1 |        0 |
|       10 | kopiya   |      1 |      180 |
|       23 | kopiya-2 |      1 |        0 |
|       27 | kopiya-2 |      0 |        0 |
+----------+----------+--------+----------+

Таблица отправленных уведомлений - "sms":
+----------+----------+----------+------+--------+---------------------+
| key_     | order_id | offer    | type | status | date                |
+----------+----------+----------+------+--------+---------------------+
| 123      |        6 | kopiya   | text |      1 | 2016-06-20 00:00:00 |
| 984140   |        6 | kopiya-3 | text |      1 | 2016-06-21 00:00:00 |
| 555      |       10 | kopiya   | text |      1 | 2016-06-27 00:00:00 |
| 8787897  |       27 | kopiya-2 | text |      1 | 2016-06-21 00:00:00 |
| 5245844  |       27 | kopiya-2 | text |      1 | 2016-06-14 00:00:00 |
| 78412544 |        6 | kopiya-3 | text |      1 | 2016-06-29 00:00:00 |
+----------+----------+----------+------+--------+---------------------+

После выполнения запроса должно получится это:
+----------+----------+
| order_id | offer    |
+----------+----------+
|        6 | kopiya   |
|       23 | kopiya-2 |
|       27 | kopiya-2 |
+----------+----------+

Решение задачи: «Запрос из двух таблиц»

Листинг программы

mysql> select * from orders;
+----------+----------+--------+----------+
| order_id | offer    | active | timezone |
+----------+----------+--------+----------+
|        6 | kopiya   |      1 |        0 |
|        6 | kopiya-3 |      1 |        0 |
|       10 | kopiya   |      1 |      180 |
|       23 | kopiya-2 |      1 |        0 |
|       27 | kopiya-2 |      0 |        0 |
+----------+----------+--------+----------+
5 rows in set (0.00 sec)
 
mysql> select * from sms;
+---------+----------+----------+------+--------+---------------------+
| key_    | order_id | offer    | type | status | date                |
+---------+----------+----------+------+--------+---------------------+
| 1       |        6 | kopiya   | text |      1 | 2016-06-20 00:00:00 |
| 2       |        6 | kopiya-3 | text |      0 | 2016-06-21 00:00:00 |
| 3       |       10 | kopiya   | text |      1 | 2016-06-27 00:00:00 |
| 4       |       27 | kopiya-2 | text |      1 | 2016-06-21 00:00:00 |
| 5       |       27 | kopiya-2 | text |      1 | 2016-06-14 00:00:00 |
| 6       |        6 | kopiya-3 | text |      1 | 2016-06-23 00:00:00 |
| 7878    |        6 | kopiya-4 | text |      1 | 2016-06-01 00:00:00 |
| 8781744 |        6 | kopiya-4 | text |      1 | 2016-06-30 00:00:00 |
| 878744  |        6 | kopiya-4 | text |      1 | 2016-06-10 00:00:00 |
+---------+----------+----------+------+--------+---------------------+
9 rows in set (0.00 sec)
 
mysql> SELECT o.order_id, o.offer, max(date) as last_date, status
    -> FROM orders AS o
    -> LEFT JOIN sms AS s
    -> ON o.order_id=s.order_id AND o.offer=s.offer
    -> WHERE `active` = 1
    -> GROUP BY o.order_id, o.offer
    -> HAVING
    -> MAX(DATE)<now()-INTERVAL 5 DAY
    -> OR MAX(DATE) IS NULL;
+----------+----------+---------------------+--------+
| order_id | offer    | last_date           | status |
+----------+----------+---------------------+--------+
|        6 | kopiya   | 2016-06-20 00:00:00 |      1 |
|        6 | kopiya-3 | 2016-06-23 00:00:00 |      0 |
|       10 | kopiya   | 2016-06-27 00:00:00 |      1 |
|       23 | kopiya-2 | NULL                |   NULL |
+----------+----------+---------------------+--------+
4 rows in set (0.00 sec)

Объяснение кода листинга программы

Запрос SELECT * FROM orders; выводит все строки из таблицы orders. Результат запроса содержит столбцы order_id, offer, active и timezone.
Запрос SELECT * FROM sms; выводит все строки из таблицы sms. Результат запроса содержит столбцы key_, order_id, offer, type, status, date.
Запрос SELECT o.order_id, o.offer, max(date) as last_date, s.status FROM orders AS o LEFT JOIN sms AS s ON o.order_id=s.order_id AND o.offer=s.offer WHEREactive= 1 GROUP BY o.order_id, o.offer HAVING MAX(DATE)<now()-INTERVAL 5 DAY OR MAX(DATE) IS NULL; объединяет таблицы orders и sms по условиям o.order_id=s.order_id AND o.offer=s.offer. Выбирает столбцы order_id, offer, last_date и status. Фильтрует результаты по условию WHEREactive= 1. Группирует результаты по столбцам o.order_id и o.offer. Применяет условие HAVING для фильтрации результатов:
- MAX(DATE)<now()-INTERVAL 5 DAY - выбирает только те строки, где дата в столбце date меньше текущей даты минус 5 дней.
- OR MAX(DATE) IS NULL - выбирает только те строки, где дата в столбце date равна NULL.