Mysql - Order by two columns

I have two tables:

events: id, name, date_from, date_to

event_recurrences: id, event_id, date_from, date_to

event records:

1, 'test 1', '2011-03-03 23:00:00', NULL
2, 'test 2'开发者_JAVA百科, '2011-03-05 23:00:00', NULL
3, 'test 3', '2011-03-12 23:00:00', NULL

event recurrences records:

1, 1, 2011-03-10 23:00:00, NULL
2, 1, 2011-03-17 23:00:00, NULL
3, 1, 2011-03-24 23:00:00, NULL

Now I'd like to get all event and get the sorting right which is my problem right now.

my approach:

SELECT e.*, r.*
FROM events AS e
LEFT OUTER JOIN event_recurrences AS r ON (e.id = r.event_id)
ORDER BY r.date_from, e.date_from

The problem is now that the records are not ordered correctly. Records without recurrences are always sorted before ones with.

Could you help to get the dates ordered ascending without prioritising records without recurrences.

You can use this syntax to put NULLs at the bottom:

ORDER BY IF (ISNULL(my_field), 1, 0), my_field;

Other database systems have something like NULLS LAST/NULLS FIRST

Do something like

ORDER BY least(r.date_from, e.date_from)

or

ORDER BY greatest(r.date_from, e.date_from)

I don't know if that's exactly what you need, but it will give you an idea...

problem is you joined events and then reoccurred them, if they dont have reoccurrences, the events that dont reoccur will not be ordered first, due to the fact that you have said to sort by the reoccurred event first, which if it didnt would be NULL, NULL is less than everything.

Only way I could see you getting the answer you wanted would be:

select * from e union 
select (r.event_id as id, e.event, t.date_from from r 
   left join e on r.event_id=e.id) 
order by date_from

which should join the tables into one long list and sort by date