A bit complicated query, how to?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: A bit complicated query, how to?

Hybrid View

  1. #1
    Join Date
    Mar 2009
    Posts
    3

    A bit complicated query, how to?

    Hello everybody, I have following problem to solve...

    There are 3 possible states, open, closed, sold out.
    I need to select hotels where in the given dates the state is open in whole date. If there is even one day closed and all rooms in given hotel are closed, hotel shouldn't be selected.

    Now I have something like:
    SELECT * FROM hotels WHERE id NOT IN (SELECT id_hotel FROM accommodation_states WHERE fromDate <= ? AND toDate >= ? AND state = 2);

    Now a problem occurs. This disables hotels where even only one room is closed and all others can be opened. That's wrong.

    An introduction, tables:
    Code:
    mysql> describe accommodation_states
    +--------------+---------+------+-----+----------+----------------+
    | Field           | Type    | Null | Key | Default | Extra            |
    +--------------+---------+------+-----+----------+----------------+
    | id               | int(11) | NO   | PRI | NULL    | auto_increment |
    | id_hotel     | int(11) | NO   |       | NULL    |                      |
    | id_room     | int(11) | NO   |       | NULL    |                      |
    | fromDate   | date     | NO  |       | NULL     |                     |
    | toDate       | date     | NO  |       | NULL     |                     |
    | state         | int(11)  | NO  |       | NULL     |                     |
    +-------------+----------+------+-----+----------+----------------+
    
    
    mysql> describe hotels;
    +---------------------------+----------------+-------+-----+----------+----------------+
    | Field                           | Type             | Null  | Key | Default | Extra            |
    +---------------------------+----------------+-------+-----+----------+----------------+
    | id                               | int(11)           | NO   | PRI | NULL    | auto_increment |
    | name                         | varchar(255) | NO   |        | NULL    |                     |
    | ...
    | publish                       | tinyint(1)      | NO   |         | 0         |                     |
    +---------------------------+----------------+------+-------+---------+----------------+


    Example of data:

    Code:
     mysql> select * from accommodation_states where state = 2 and fromDate <= '2009-03-25' and toDate >= '2009-03-26';
    +------+----------+---------+--------------+----------------+-------+
    | id   | id_hotel | id_room | fromDate  | toDate         | state |
    +------+---------+---------+---------------+----------------+-------+
    | 1925 |       93 |      16 | 2009-03-21 | 2009-05-01 |     2 |
    | 1879 |     153 |    368 | 2009-03-24 | 2009-03-28 |     2 |
    | 2237 |       36 |      59 | 2009-03-10 | 2009-05-26 |     2 |
    | 2240 |       36 |      60 | 2009-03-10 | 2009-05-26 |     2 |
    | 2243 |       36 |      61 | 2009-03-10 | 2009-05-26 |     2 |
    | 2392 |       88 |      69 | 2009-03-10 | 2009-06-30 |     2 |
    | 2394 |       88 |      71 | 2009-03-10 | 2009-06-30 |     2 |
    | 2396 |       88 |      70 | 2009-03-10 | 2009-06-30 |     2 |
    | 2398 |       88 |      72 | 2009-03-10 | 2009-06-16 |     2 |
    | 3056 |       40 |      53 | 2009-03-15 | 2009-05-28 |     2 |
    | 3255 |      114 |     102 | 2009-03-23 | 2009-03-28 |     2 |
    +------+----------+---------+------------+------------+-------+
    Now all these hotels are closed no matter what. They should be closed only when all rooms are in state 2 ...

  2. #2
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    You need to reverse your logic and look for open rooms for your date. Try this:

    SELECT * FROM hotels WHERE id IN (SELECT id_hotel FROM accommodation_states WHERE fromDate >= ? AND toDate <= ? AND state = 1);

  3. #3
    Join Date
    Mar 2009
    Posts
    3
    Thank you very much for you reply. I have tried this, but there is another problem with this solution.

    For example client selects date 2009-03-20 - 2009-03-25

    In databaase is saved:

    2009-03-10 - 2009-03-23 state: 1
    2009-03-24 - 2009-03-26 state: 2

    In this case it would be presented as opened, anyway there is one day closed (2009-03-24) and it means it can't be displayed.

    (Last day (check-out) isn't important for this.)

  4. #4
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    Ok you are using a date range so the fromDate must be <= your start date and the toDate needs to be >= your end date and the state must = 1 which indicates the fromDate and toDate are open.

    So your original Date logic was correct, but the state needed to be 1 and the id needed to be 'In' the subquery where you were doing 'Not In' before.

    This version should now work:
    SELECT * FROM hotels WHERE id IN (SELECT id_hotel FROM accommodation_states WHERE fromDate <= ? AND toDate >= ? AND state = 1);

Similar Threads

  1. Union query
    By peljo in forum Database
    Replies: 4
    Last Post: 12-17-2007, 12:38 PM
  2. SQL2000 remote SQL query
    By lightningtechie in forum Database
    Replies: 1
    Last Post: 02-07-2006, 09:34 AM
  3. bad syntax?
    By MacataQ in forum VB Classic
    Replies: 4
    Last Post: 07-26-2005, 03:24 PM
  4. Multi-table Select Query using Joins
    By knightsg in forum Database
    Replies: 0
    Last Post: 02-16-2005, 09:07 AM
  5. reg...select count distinct....query
    By Mohan Ekambaram in forum Database
    Replies: 0
    Last Post: 12-09-2001, 12:56 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center