I
I
ivazin2014-09-26 17:20:02
MySQL
ivazin, 2014-09-26 17:20:02

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

2 answer(s)
S
Sergey Senkevich, 2014-09-26
@ssenkevich

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

A
Alexey Skahin, 2014-09-29
@pihel

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 question

Ask a Question

731 491 924 answers to any question