mysql - Comparing Dates In Sub Query Returns No Results -

i've got mysql query:

select some_fields,        (select slug              photos              date_added > p.date_added              order date_added asc        ) next,         (select slug              photos              date_added < p.date_added              order date_added desc        ) prev     photos p      slug = 'test' 

to photos table, next , previous images tagging along in same query. if remove 'prev' part of query runs , returns result, 'prev' part no results retruned reason.

weirdly, if switch < > in 'prev' part returns (an incorrect) result, there's problem comparing 2 datetimes that.

i'm @ wit's end honest, no idea be...

i'd prefer join photos table itself, not least because not has "next" photo, want outer join. here's query doesn't work:

select some_fields, next.slug, prev.slug photos current  left join photos next on (current.date_added < next.date_added) left join photos prev on (current.date_added > prev.date_added) current.slug = 'test' 

it fails because returns triples of photos middle 1 between other two. them "next" , "previous" ones, can try find photos inbetween our current , next, , inbetween our current , prev, , make sure no such photos exist!

select some_fields, next.slug, prev.slug 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                              , betternext.date_added < next.date_added) left join photos betterprev on (current.date_added > betterprev.date_added                              , betterprev.date_added > prev.date_added) current.slug = 'test' , betternext.slug null , betterprev.slug null 

this ensures next , prev records best ones -- there no betternext , no betterprev.