how to prevent MySql Cross Join Query returning zero results if one of the queries returns null

Question

I use this query to fetch the ID of the Next and Previous Events in a mysql events table:

SELECT e.id AS current, prev.id AS previous, next.id AS next
FROM events e
CROSS JOIN 
(
    SELECT id FROM events 
    WHERE date < '{$result['date']}' 
    ORDER BY date DESC 
    LIMIT 1
) prev
CROSS JOIN 
(
    SELECT id
    FROM events 
    WHERE date > '{$result['date']}' 
    ORDER BY date 
    LIMIT 1
) next
WHERE e.date = '{$result['date']}'

This query works fine.

lets pretend the table looks like so:

ID | EVENT_NAME   | DATE
------------------------------
1  | test event 1 | 2012-01-01
2  | test event 2 | 2012-01-02
3  | test event 3 | 2012-02-03

If i run the query with $result['date'] as 2012-01-02, this is returned:

Array
(
    [0] => Array
    (
        [current] => 2
        [previous] => 1
        [next] => 3
    )
)

However if i run the query with $result['date'] as 2012-01-01 OR 2012-01-03, an empty result set is returned!

If a previous or a next date does not exist because the passed date is the highest or lowest, I still need the result set to return the next or previous

E.G.

So if i run the query with $result['date'] as 2012-01-01, i need the result set:

Array
(
    [0] => Array
    (
        [current] => 1
        [next] => 2
    )
)

OR

Array
(
    [0] => Array
    (
        [current] => 1
        [previous] => NULL
        [next] => 2
    )
)

Answer 1

You can use a LEFT JOIN with 1=1

SELECT e.id AS current, prev.id AS previous, next.id AS next 
FROM events e 
LEFT JOIN  
( 
    SELECT id FROM events  
    WHERE date < '{$result['date']}'  
    ORDER BY date DESC  
    LIMIT 1 
) ON prev 1=1 
LEFT JOIN  
( 
    SELECT id 
    FROM events  
    WHERE date > '{$result['date']}'  
    ORDER BY date  
    LIMIT 1 
) ON next 1=1 
WHERE e.date = '{$result['date']}'