MySQL get previous row with inner join

join;MySQL

In my query, I need to get the previous row with the current row and everything works fine here.

SELECT *,
       (SELECT calendar_id
       FROM   calendar AS sub
       WHERE  sub.calendar_id < calendar.calendar_id
       ORDER  BY sub.calendar_id DESC
       LIMIT  1) AS previous
FROM  calendar

But now i need to join more two tables i using inner join for what.

SELECT *,
       (SELECT calendar_id
       FROM   calendar AS sub
       WHERE  sub.calendar_id < calendar.calendar_id
       ORDER  BY sub.calendar_id DESC
       LIMIT  1) AS previous
FROM calendar
     INNER JOIN relationships
             ON calendar.relation_id = relationships.relation_id
     INNER JOIN customers
             ON customers.customer_id = relationships.customer_id
WHERE  relationships.user_id = '$user_id'
       AND Date_format(calendar.date, '%m-%Y') = '$date'
ORDER  BY calendar.date ASC

Here is my final query with inner joins, but that part about previous row now result is just mixed. How i can keep my previous row result good and have that data from other tables with inner join?

Best Answer

Use that query as a 'derived table' to finish the task:

SELECT ...
    FROM ( ... your query ... ) AS x
    JOIN calendar AS prev_row  ON prev_row.calendar_id = x.previous
    ...

(If you are using MySQL 8.0, there is a "windowing" way to get the previous id.)

Related Question