0

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?

  • What does "mixed" mean? Don't you get the results you want? – ypercubeᵀᴹ Apr 18 '18 at 17:36
  • 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. – RDFozz Apr 18 '18 at 17:48
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.