CODEWITHSUNDEEP
mysqldate
m0dE
m0dE

Reputation: 313

Creating a MySQL query that deals with calendar with multiple events

I'm 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 2 tables:

Venue Table:

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

Could anyone suggest the best way of approaching this problem?

My current approach is:

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 am not getting desired results... I'm getting the below instead:

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?

Upvotes: 4

Views: 603

Answers (1)

Mateus Schneiders
Mateus Schneiders

Reputation: 4903

The weird thing is, your select statement doesn't even return the columns named as in the result set you posted. Also, i noticed you are repeating the day '2012-10-02' on your query.

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 bellow share the same venue_id and venue field, and your query is grouping by venue, this way its suppressing the OCCUPIED result on 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 could try adding an aggregate function like MAX to get any OCCUPIED occurrence contained on the 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

Upvotes: 2

Related Questions