Creating a MySQL query related to a multi-event calendar

Question

I am trying to create a query that produces:

VENUE | 2012-10-01 | 2012-10-02 | 2012-10-03 01 | OCCUPIED | EMPTY | OCCUPIED 02 | EMPTY | EMPTY | OCCUPIED 03 | OCCUPIED | EMPTY | EMPTY 

from these two tables:

Table of places:

 venue_id | venue 1 | 01 2 | 02 3 | 03 

Event table:

 event_id | start | end | venue_id 1 | 2012-10-01 | 2012-10-02 | 1 2 | 2012-10-03 | 2012-10-04 | 1 3 | 2012-10-03 | 2012-10-04 | 2 4 | 2012-10-01 | 2012-10-02 | 3 

Can anyone suggest a better way to approach this issue?

My current approach:

 SELECT venue, IF(start <= '2012-10-01' AND '2012-10-01' < end, 'OCCUPIED','EMPTY') AS '2012-10-01', IF(start <= '2012-10-02' AND '2012-10-02' < end, 'OCCUPIED','EMPTY') AS '2012-10-02', IF(start <= '2012-10-03' AND '2012-10-03' < end, 'OCCUPIED','EMPTY') AS '2012-10-03' FROM Venue as v, Event as e WHERE e.venue_id = v.venue_id GROUP BY v.venue 

However, I do not get the desired results ... Instead, I get the following:

 VENUE | 2012-10-01 | 2012-10-02 | 2012-10-03 01 | OCCUPIED | EMPTY | EMPTY 02 | EMPTY | EMPTY | OCCUPIED 03 | OCCUPIED | EMPTY | EMPTY 

What am I doing wrong?

Answer 1

The strange thing is that your select statement does not even return columns with the name specified in the result set that you appeared. In addition, I noticed that you are repeating the day '2012-10-02' at your request.

 SELECT IF(start <= '2012-10-01' AND '2012-10-01' < end, 'OCCUPIED','EMPTY') AS '2012-10-01', IF(start <= '2012-10-02' AND '2012-10-02' < end, 'OCCUPIED','EMPTY') AS '2012-10-02', IF(start <= '2012-10-03' AND '2012-10-03' < end, 'OCCUPIED','EMPTY') AS '2012-10-03' FROM Venue as v, Event as e WHERE e.venue_id = v.venue_id GROUP BY v.venue 

EDIT:

Both events below have the same venue and venue, and your request is grouped by venue, so it suppresses the OCCUPIED result on the day '2012-10-03'.

 event_id | start | end | venue_id 1 | 2012-10-01 | 2012-10-02 | 1 2 | 2012-10-03 | 2012-10-04 | 1 

SQL FIDDLE: http://sqlfiddle.com/#!2/dc33f/20

You can try adding an aggregate function like MAX to get any OCCUPIED event contained in a subset of records:

 SELECT e.*, v.venue, max(IF(start <= '2012-10-01' AND '2012-10-01' < end, 'OCCUPIED','EMPTY')) AS '2012-10-01', max(IF(start <= '2012-10-02' AND '2012-10-02' < end, 'OCCUPIED','EMPTY')) AS '2012-10-02', max(IF(start <= '2012-10-03' AND '2012-10-03' < end, 'OCCUPIED','EMPTY')) AS '2012-10-03' from event e, venue v where e.venue_id = v.venue_id GROUP BY v.venue 

SQL FIDDLE: http://sqlfiddle.com/#!2/dc33f/22