MySQL joining four tables and using WEEK and DATEDIFF to get current data?

Question

Hi!

I'm trying to write a query getting information from four tables and I'm not able to figure it out. Can someone please help me with some guidance?

The model is that a user can book a room for a specified time period. I would like to retrieve the data that is current for the next seven days so I'm using WEEK and DATEDIFF. The DATEDIFF is to make sure that I don't get back any room booking happening in the future or more than a year ago. I have four tables: users, rooms, times, bookings

USERS:

  CREATE TABLE IF NOT EXISTS users (
  apartmentNumber varchar(4) NOT NULL,
  surname varchar(50) NOT NULL,
  password varchar(40) NOT NULL,
  PRIMARY KEY  (apartmentNumber)
)

ROOMS:

CREATE TABLE IF NOT EXISTS rooms (
  id int(11) NOT NULL auto_increment,
  name varchar(40) NOT NULL,
  PRIMARY KEY  (id),
  KEY name (name)
)

TIMES:

CREATE TABLE IF NOT EXISTS times (
  id int(11) NOT NULL auto_increment,
  start time NOT NULL,
  stop time NOT NULL,
  PRIMARY KEY  (id),
  KEY start (start)
)

BOOKINGS:

CREATE TABLE IF NOT EXISTS bookings (
  id int(11) NOT NULL auto_increment,
  user varchar(4) NOT NULL,
  room int(11) NOT NULL,
  date date NOT NULL,
  time int(11) NOT NULL,
  PRIMARY KEY  (id),
  KEY room (room),
  KEY time (time),
  KEY user (user)
)

I've populated these tables with the following data:

INSERT INTO bookings (id, user, room, date, time) VALUES
(1, '0379', 1, '2009-11-19', 1),
(2, '0379', 1, '2009-11-23', 2),
(3, '0379', 1, '2009-11-14', 3),
(4, '0379', 2, '2009-11-23', 3),
(5, '0379', 2, '2009-11-19', 2),
(6, '0379', 2, '2009-11-20', 1);

ALTER TABLE bookings
  ADD CONSTRAINT bookings_ibfk_2 FOREIGN KEY (room) REFERENCES rooms (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT bookings_ibfk_3 FOREIGN KEY (user) REFERENCES users (apartmentNumber) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT bookings_ibfk_4 FOREIGN KEY (time) REFERENCES times (id) ON DELETE CASCADE ON UPDATE CASCADE;

INSERT INTO rooms (id, name) VALUES
(1, 'Room 1'),
(2, 'Room 2');

INSERT INTO times (id, start, stop) VALUES
(1, '07:00:00', '12:00:00'),
(2, '12:00:00', '17:00:00'),
(3, '17:00:00', '22:00:00');

INSERT INTO users (apartmentNumber, surname, password) VALUES
('0379', 'Smith', 'password');

Before adding the times table (as the time were previously in the bookings table) I managed to get the data out with the following query:

SELECT users.surname, users.apartmentNumber, rooms.name as room, bookings.date, bookings.time 
FROM bookings, rooms, users
WHERE WEEK(NOW(), 7) = WEEK(bookings.date, 7)
AND DATEDIFF(NOW(), bookings.date) < 1
AND DATEDIFF(NOW(), bookings.date) > -10
AND users.apartmentNumber = bookings.user
AND bookings.room = rooms.id
ORDER BY room, bookings.time, bookings.date ASC;

But after breaking out the times to a separate table I'm not able to do it:

SELECT users.surname, users.apartmentNumber, rooms.name as room, bookings.date, times.start, times.stop
FROM bookings, rooms, users, times
WHERE WEEK(NOW(), 7) = WEEK(bookings.date, 7)
AND DATEDIFF(NOW(), bookings.date) < 1
AND DATEDIFF(NOW(), bookings.date) > -10
AND users.apartmentNumber = bookings.user
AND bookings.room = rooms.id
AND times.id = bookings.time
ORDER BY room, bookings.time, bookings.date ASC;

The result set from the above query is:

Smith       379  Room 2  2009-11-20  07:00:00  12:00:00

but it should include the two bookings on 2009-11-23

I'm lost... any comments will be appreciated. Thanks!

/Anders

Answer 1

Your use of the WEEK() function limits things to THIS week, and 2009-11-23 is in NEXT week. This is incompatible with your stated

retrieve the data that is current for the next seven days

I haven't completely analyzed your query but I think you should experiment with changing the range on your DATEDIFF() function from (-10,1) to (-10,+7) and remove WEEK altogether.

A couple of side comments:

1. You should use the more modern JOIN syntax, which is easier to read
2. express your date range using BETWEEN

Answer 2

The problem with using WEEK() is that it gives a fixed 7 day window, not a sliding 7 day window which is what it appears you need. Try this:

SELECT users.surname, users.apartmentNumber, rooms.name as room, bookings.date, times.start, times.stop
FROM bookings, rooms, users, times
WHERE bookings.date >= CURDATE()
AND bookings.date <= DATE_ADD(CURDATE(), INTERVAL 7 DAY)
AND users.apartmentNumber = bookings.user
AND bookings.room = rooms.id
AND times.id = bookings.time
ORDER BY room, bookings.time, bookings.date ASC;

Depending on whether you want it to be inclusive/exclusive and exactly what you mean by a week, you may need to adjust the edge cases (> instead of >= or INTERVAL 8 DAY for instance).