SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Complex multi-table join

    Apolgies for this being a long post

    MySQL Version 4.0.21-debug-log

    Code:
         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

    Code:
         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
    Code:
        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

    Code:
      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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no problem

    actually, i do use them, but only when forced to
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •