Comparing Dates In Sub Query Returns No Results

I've got a MySQL query:

SELECT some_fields,
       (SELECT slug 
            FROM photos 
            WHERE date_added > p.date_added 
            OR开发者_运维知识库DER BY date_added ASC
       ) AS next, 
       (SELECT slug 
            FROM photos 
            WHERE date_added < p.date_added 
            ORDER BY date_added DESC
       ) AS prev
    FROM photos p 
    WHERE slug = 'test'

To get some photos from a table, with the next and previous images tagging along in the same query. If I remove the 'prev' part of the query it runs and returns a result, however with the 'prev' part no results are retruned for some reason.

Weirdly, if I switch the < to a > in the 'prev' part it then returns (an obviously incorrect) result, so there's a problem with comparing those two datetimes like that.

I'm at my wit's end to be honest, no idea what it could be...

I'd much prefer you JOIN your Photos table to itself, not least because not everything has a "next" photo, so you actually want an outer join. Here's a query that doesn't work:

SELECT some_fields, next.slug, prev.slug
FROM photos current 
LEFT JOIN photos next ON (current.date_added < next.date_added)
LEFT JOIN photos prev ON (current.date_added > prev.date_added)
WHERE current.slug = 'test'

It fails because it returns all triples of photos where the middle one is between the other two. To get them to be the "next" and "previous" ones, we can also try to find photos inbetween our current and next, and inbetween our current and prev, and make sure that no such photos exist!

SELECT some_fields, next.slug, prev.slug
FROM photos current 
LEFT JOIN photos next ON (current.date_added < next.date_added)
LEFT JOIN photos prev ON (current.date_added > prev.date_added)
LEFT JOIN photos betternext ON (current.date_added < betternext.date_added 
                            AND betternext.date_added < next.date_added)
LEFT JOIN photos betterprev ON (current.date_added > betterprev.date_added 
                            AND betterprev.date_added > prev.date_added)
WHERE current.slug = 'test'
AND betternext.slug IS NULL
AND betterprev.slug IS NULL

This ensures that your next and prev records are the best ones -- that there is no betternext and no betterprev.