MySQL get previous row with inner join

Question

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?

• ypercubeᵀᴹ: What does "mixed" mean? Don't you get the results you want?
• RDFozz: If the problem is that sometimes the previous row isn't included in the result set, then you need to perform the joins with the previous sub-query, as well as in the main query.

Answer 1

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.)