MySQL JOIN on MAX

Question

I have two of my SQL tables that I'm trying to join, they are simplified as:

+----------------------------+
| customers                  |
+-------------+-------+------+
| customer_id | first | last |
+-------------+-------+------+
| 0           | John  | Doe  |
+-------------+-------+------+
| 1           | Jane  | Doe  |
+-------------+-------+------+

+-------------------------------------------------------------------+
| contact_log                                                       |
+----------------+-------------+--------------+---------------------+
| contact_log_id | customer_id | contact_type | date_time           |
+----------------+-------------+--------------+---------------------+
| 0              | 0           | email        | 2016-05-17 03:21:45 |
+----------------+-------------+--------------+---------------------+
| 1              | 0           | phone        | 2016-05-17 16:11:35 |
+----------------+-------------+--------------+---------------------+
| ...            | ...         | ...          |                     |
+----------------+-------------+--------------+---------------------+

I need a request that will select customers and their last time and type of contact. I tried this query:

SELECT
    `customers`.`customer_id`,
    `customers`.`first`,
    `customers.last`,
    `contact_log`.`contact_type`,
    MAX(`contact_log`.`date_time`)
FROM
    `customers`
JOIN
    `contact_log`
ON
    `customers`.`customer_id` = `contact_log`.`customer_id`

It usually sorts incorrectly date_time. Investigating the problem, in some versions of MySQL there is an error, where MAXthey MINwork incorrectly with DATETIME. Therefore a workaround

MAX(CAST(`contact_log`.`date_time` AS CHAR))

So, I get client strings with the latter date_time. However, it contact_typedoes not correspond to the time. In the examples below, my result is as follows:

+-------------+-------+------+--------------+---------------------+
| customer_id | first | last | contact_type | date_time           |
+-------------+-------+------+--------------+---------------------+
| 0           | John  | Doe  | email        | 2016-05-17 16:11:35 |
+-------------+-------+------+--------------+---------------------+

contact_type date_time contact_log. , SELECT/JOIN, . ( n + 1), .

contact_type date_time?

, , . . , ?

Answer 1

- , , , , customer_id:

SELECT * FROM
(
    SELECT
        customers.customer_id,
        customers.first,
        customers.last,
        contact_log.contact_type,
        contact_log.date_time

        FROM customers
            INNER JOIN contact_log ON contact_log.customer_id = customers.customer_id -- or LEFT JOIN - see comment

        ORDER BY contact_log.date_time DESC
) logs GROUP BY logs.customer_id

, , ..

. , "" . , GROUP BY, ORDER BY "".

CREATE VIEW logs AS 
    SELECT
        customers.customer_id,
        customers.first,
        customers.last,
        contact_log.contact_type,
        contact_log.date_time

        FROM customers
            LEFT JOIN contact_log ON contact_log.customer_id = customers.customer_id

        GROUP BY
            customers.customer_id,
            contact_log.date_time DESC,
            contact_log.contact_type DESC;

CREATE VIEW testview AS SELECT * FROM logs GROUP BY logs.customer_id;

SELECT * FROM testview;

Answer 2

, MAX GROUP BY, , .
, :

SELECT
    customers.customer_id,
    customers.first,
    customers.last,
    max_contact_log.contact_type,
    max_contact_log.date_time
FROM
    customers
JOIN
    (select customer_id, contact_type, max(date_time) AS date_time
      FROM contact_log GROUP BY customer_id
    ) as max_contact_log 
ON
    customers.customer_id = max_contact_log.customer_id;

Answer 3

Without a subquery, the solution here is having:

select c.*, cl.contact_type, cl.date_time
from customers c
join contact_log cl
on c.customer_id = cl.customer_id
left join contact_log t
on cl.customer_id = t.customer_id
and cl.date_time <= t.date_time
group by c.customer_id, c.`first`, c.`last`, cl.contact_type, cl.date_time
having count(*) <= 1