Answer the question
In order to leave comments, you need to log in
MySQL JOIN with Nth row selection of join table?
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 the question
In order to leave comments, you need to log in
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
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
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question