group by with limit and hopefully no empty sets

Question

I've got a (for my abilities) rather complex query that I am trying to improve upon. I use the same query multiple times, changing only some parameters, so I'm hoping to not need a full-re-write.

I am searching for a bunch of events by date and location. I put these into an html table where

Date 1            Date2        Date3
event1/1         event1/2      event1/3
event1/2         event2/2      event1/3
event1/3         event2/3      event3/3

Hopefully that gives you an idea of the output and grouping.

Right now I commit 3 queries, one for each day, and I pass in the date field. What I'm hoping to be able to do is two things 1) provide a date range so I make only one query 2) if a result group is empty (so nothing on date3), the response will return date4

Here's what I've got so far, but it doesn't achieve objective2 (empty groups), and it doesn't seem to be returning the right number in the date range, since I limit the number of results (though even without the limit, i'm not getting the right result

I've simplified the query a bit so that it is hopefully easier to understand, but the structure is exactly the same

SELECT event.stuff,  user.stuff, eventdate.stuffshows , 
   GROUP_CONCAT( user.groupstuff ) AS myStuff
FROM events
JOIN users ON events.id = users.bid
WHERE location= location
AND event.date
BETWEEN '2009-08-14'
AND '2009-08-17'
GROUP BY event.date
ORDER BY event.date, user.id DESC
LIMIT 0 , 5
) mystuff ON event.bid = similar.id
AND event.date=similar.date

So what I'm looking for essentially is this kind of out (similar to what I have above)

date1 / event1
date1 / event2
date1 / event3
date1 / event4
date1 / event5
date2 - empty - nothing returned, go to date3
date3 / event1
date3 /event2
date3 / event3
date4 / event1
date4 / event2
date5 - empty - nothing returned, go to date6
date6 / event1

I hope this is clear, and that it's possible.

Answer 1

You might be able to do this with a HAVING in the subquery:

GROUP BY event.date
ORDER BY event.date, user.id DESC
HAVING event.stuff
LIMIT 0 , 5