CODEWITHSUNDEEP
mysql
php_nub_qq
php_nub_qq

Reputation: 16055

Select previous and next records in join statement

On the page for viewing posts I thought it would be a nice idea to have links for the next and previous posts. I thought I should get those 3 records in 1 query since I'm so smart and I had a bunch of time to waste anyway. So as you probably guessed I couldn't do it, but I'm interested to find the solution. Here is what I have

SELECT a.id,
       a.title,
       a.body,
       p.id    AS prev_id,
       p.title AS prev_title,
       n.id    AS next_id,
       n.title AS next_title
FROM   posts a
       LEFT JOIN posts p
              ON p.id < a.id
       LEFT JOIN posts n
              ON n.id > a.id
WHERE  a.id = ?
LIMIT  1

The problem is that for prev_id and prev_title I always get the first record in the table. I tried adding ORDER BY but it didn't really seem to affect the join. How can I select the previous and not the first record using a join like in the example?

Upvotes: 0

Views: 198

Answers (1)

user2941651
user2941651

Reputation:

I hope that this one you might find helpful:

SELECT a.id,
       a.title,
       a.body,
       p.id    AS prev_id,
       p.title AS prev_title,
       n.id    AS next_id,
       n.title AS next_title
FROM   posts a
       INNER JOIN
       (
            SELECT 
                _a.id AS RefID,
                MIN(_a.id - _p.id) AS MinDistPrev,
                MIN(_n.id - _a.id) AS MinDistNext
            FROM   
                posts _a
                LEFT JOIN posts _p
                      ON _p.id < _a.id
                LEFT JOIN posts _n
                      ON _n.id > _a.id
            WHERE  
                _a.id = ?
            GROUP BY
                _a.id
       ) AS _PrevNextDist
        ON _PrevNextDist.RefID = a.ID
       LEFT JOIN posts p
              ON p.id < a.id
                 AND a.id - p.id = _PrevNextDist.MinDistPrev
       LEFT JOIN posts n
              ON n.id > a.id
                 AND n.id - a.id = _PrevNextDist.MinDistNext
LIMIT  1

Additionally, adding ORDER BY p.id DESC to the original code solved the problem as well

SELECT a.id,
       a.title,
       a.body,
       p.id    AS prev_id,
       p.title AS prev_title,
       n.id    AS next_id,
       n.title AS next_title
FROM   posts a
       LEFT JOIN posts p
              ON p.id < a.id
       LEFT JOIN posts n
              ON n.id > a.id
WHERE  a.id = ?
ORDER BY p.id DESC
LIMIT  1

Upvotes: 1

Related Questions