MySQL JOIN with Nth row selection of join table?

Question

MySQL

I have two tables:
1) users (id, ...)
2) orders (id, user_id, date, ...)
I need to supplement the first table with a column with the date of the second order or NULL. Is this possible on MySQL?

Answer 1

The problem, of course, is not for MySQL, but there is a solution:

SELECT u.*, t2.date FROM users u LEFT JOIN (
    SELECT @rn := IF(t1.prevdate IS NULL, 1, @rn + 1) rn, t1.id, t1.user_id, t1.date FROM (
        SELECT o.id, o.user_id, o.date, o2.date prevdate
        FROM orders o 
            LEFT JOIN orders o2 ON o2.user_id = o.user_id AND o2.date < o.date
            LEFT JOIN orders o3 ON o3.user_id = o.user_id AND o3.date > o2.date AND o3.date < o.date
        WHERE o3.id IS NULL ORDER BY o.user_id, o.date
    ) t1, (SELECT @rn := 0) sg
) t2 ON t2.user_id = u.id AND t2.rn = 2

Answer 2

Maybe so

SELECT u.*,
(
  SELECT MAX(o1.date) 
  FROM orders o
  INNER JOIN orders o1
   ON(o1.user_id = o.user_id
       AND o1.date < o.date)
  WHERE 
    o.user_id = u.id
) as date
FROM users u