Getting next and previous record in a join

Question

I am currently using this query to select a media record in my PHP application and get the associated user data (if available):

SELECT media.*,
user.*
FROM media_table AS media
LEFT JOIN user_table AS user ON
    (user.user_id = media.user_id)
WHERE media.media_id = {$mediaId}

What I'm interested in doing is selecting media.* for the next available media_table record and the previous media_table record. I could do this based on the media_id itself (media_table.media_id is auto increment) or there are other columns available such as media_date (this may be more accurate - it's an INT column in epoch time format).

And I would like to do this (if possible) via a join so I don't have to run multiple queries.

It reminds me of this answer: https://stackoverflow.com/a/1446831/1593325

It gave me some idea, but I couldn't get it to work (unless I ran separate queries).

Any help is much appreciated. Thank you so much for your time.

Answer 1

If you wish to get all the 3 rows at the same time, you can modify your WHERE clause to accommodate your requirements. The modified query would be :

SELECT media.*,
user.*
FROM media_table AS media
LEFT JOIN user_table AS user ON
    (user.user_id = media.user_id)
WHERE (media.media_id = {$mediaId} 
       or media.media_id = ({$mediaId}-1) 
       or media.media_id = ({$mediaId}+1))

Update : In that case you can use the query as

SELECT media.*,
user.*
FROM media_table AS media
LEFT JOIN user_table AS user ON
    (user.user_id = media.user_id)
WHERE (media.media_id = {$mediaId} 
       or media.media_id = (SELECT MIN(media_id ) FROM media_table 
                           where media_id > {$mediaId}) 
       or media.media_id = (SELECT MAX(media_id ) FROM media_table
                           where media_id < {$mediaId})
      )