Complex multi-table join

[swdev]

Apolgies for this being a long post

MySQL Version 4.0.21-debug-log

     Tables
     
     --
     -- Table structure for table `bookings`
     --
     
     CREATE TABLE bookings (
       id int(11) NOT NULL auto_increment,
       room_id tinyint(4) NOT NULL default '0',
       datein date NOT NULL default '0000-00-00',
       dateout date NOT NULL default '0000-00-00',
       PRIMARY KEY  (id)
     ) TYPE=MyISAM;
     
     --
     -- Dumping data for table `bookings`
     --
     
     INSERT INTO bookings VALUES (1,1,'2005-02-17','2005-02-19');
     
     --
     -- Table structure for table `integers`
     --
     
     CREATE TABLE integers (
       _i int(11) NOT NULL default '0',
       PRIMARY KEY  (_i)
     ) TYPE=MyISAM;
     
     --
     -- Dumping data for table `integers`
     --
     
     INSERT INTO integers VALUES (0);
     INSERT INTO integers VALUES (1);
     INSERT INTO integers VALUES (2);
     INSERT INTO integers VALUES (3);
     INSERT INTO integers VALUES (4);
     INSERT INTO integers VALUES (5);
     INSERT INTO integers VALUES (6);
     INSERT INTO integers VALUES (7);
     INSERT INTO integers VALUES (8);
     INSERT INTO integers VALUES (9);
     INSERT INTO integers VALUES (10);
     INSERT INTO integers VALUES (11);
     INSERT INTO integers VALUES (12);
     INSERT INTO integers VALUES (13);
     INSERT INTO integers VALUES (14);
     INSERT INTO integers VALUES (15);
     INSERT INTO integers VALUES (16);
     INSERT INTO integers VALUES (17);
     INSERT INTO integers VALUES (18);
     INSERT INTO integers VALUES (19);
     INSERT INTO integers VALUES (20);
     INSERT INTO integers VALUES (21);
     INSERT INTO integers VALUES (22);
     INSERT INTO integers VALUES (23);
     INSERT INTO integers VALUES (24);
     INSERT INTO integers VALUES (25);
     INSERT INTO integers VALUES (26);
     INSERT INTO integers VALUES (27);
     INSERT INTO integers VALUES (28);
     INSERT INTO integers VALUES (29);
     INSERT INTO integers VALUES (30);
     INSERT INTO integers VALUES (31);
     
     --
     -- Table structure for table `room_types`
     --
     
     CREATE TABLE room_types (
       room_type_id tinyint(4) NOT NULL auto_increment,
       name varchar(10) NOT NULL default '',
       PRIMARY KEY  (room_type_id),
       UNIQUE KEY name (name)
     ) TYPE=MyISAM;
     
     --
     -- Dumping data for table `room_types`
     --
     
     INSERT INTO room_types VALUES (1,'Single');
     INSERT INTO room_types VALUES (2,'Double');
     INSERT INTO room_types VALUES (3,'Family');
     
     --
     -- Table structure for table `rooms`
     --
     
     CREATE TABLE rooms (
       room_id int(11) NOT NULL auto_increment,
       room_number tinyint(4) NOT NULL default '0',
       room_type_id tinyint(4) NOT NULL default '0',
       max_occupancy tinyint(4) NOT NULL default '0',
       PRIMARY KEY  (room_id),
       UNIQUE KEY room_number (room_number)
     ) TYPE=MyISAM;
     
     --
     -- Dumping data for table `rooms`
     --
     
     INSERT INTO rooms VALUES (1,1,1,1);
     INSERT INTO rooms VALUES (2,2,2,2);
     INSERT INTO rooms VALUES (3,3,3,4);

r937 supplied this blinding piece of SQL which gets all the dates in the selected date range, and a count of the total number of rooms booked on each day

     SELECT
     	DATE_ADD('2005-02-15', interval _i day) as thedate
       , COUNT(bookings.id) as total_bookings
       , bookings.id as room_id
      FROM
        bookings
        RIGHT OUTER
        JOIN integers
     		ON DATE_ADD('2005-02-15', interval _i day)
     		   BETWEEN bookings.datein AND bookings.dateout
      WHERE
        _i
        BETWEEN 0 AND TO_DAYS('2005-02-20') - TO_DAYS('2005-02-15')
      GROUP BY
        thedate
      ORDER BY
        thedate

This gives the following results

    Date: 2005-02-15 Bookings: 0 Room id: 
     Date: 2005-02-16 Bookings: 0 Room id: 
     Date: 2005-02-17 Bookings: 1 Room id: 1
     Date: 2005-02-18 Bookings: 1 Room id: 1
     Date: 2005-02-19 Bookings: 1 Room id: 1
     Date: 2005-02-20 Bookings: 0 Room id:

because there is 1 room booked (room_id = 1) between the 17th and 19th

Give the test data, what I would like is the following

  Date		Bookings  Room Number  Room Type  Max Occupancy
 2005-02-15	 0		 1		 Single	 1
 2005-02-15	 0		 2		 Double	 2
 2005-02-15	 0		 3		 Family	 3
 2005-02-16	 0		 1		 Single	 1
 2005-02-16	 0		 2		 Double	 2
 2005-02-16	 0		 3		 Family	 3
 2005-02-17	 1		 1		 Single	 1
 2005-02-17	 0		 2		 Double	 2
 2005-02-17	 0		 3		 Family	 3
 2005-02-18	 1		 1		 Single	 1
 2005-02-18	 0		 2		 Double	 2
 2005-02-18	 0		 3		 Family	 3
 2005-02-19	 1		 1		 Single	 1
 2005-02-19	 0		 2		 Double	 2
 2005-02-19	 0		 3		 Family	 3
 2005-02-20	 0		 1		 Single	 1
 2005-02-20	 0		 2		 Double	 2
 2005-02-20	 0		 3		 Family	 3

where Room Number comes from the rooms table (rooms.room_number) and Room Type comes from the room_types table (room_types.name) and Max Occupance comes from the rooms table (rooms.max_occupancy).

Can this be done in a single query? or will I have to run multiple queries?


Later on, there will be another field in the bookings table (called guest_id) which will link to a guest table (containing guest_id, name and other data).
I will need to get that data as wwll, so if it could all be done in a single query, that will really save my bacon .


Many thanks in advance

[r937]

yes it can all be done in a single query, just join bookings to rooms and to users as normal

you say i wrote a RIGHT OUTER JOIN? incredible

i never write RIGHT OUTER JOINs

[swdev]

r937
Thanks for replying. I must profusley apologise. I checked back and you you LEFT OUTER JOINS. I changed them to RIGHT OUTER JOINS when I was trying to add the rooms table.
Thanks for confirming that it can be done.
I will try, and if (when ) I run into problems, I will post back.
Once again, apologies for saying that you wrote RIGHT OUTER JOINS.

[r937]

no problem

actually, i do use them, but only when forced to