Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2010
    Posts
    7

    Unanswered: MySQL select help

    I'm doing the following. I need this query to fail because the inv_date is not consecutive. 2016-02-04 is missing, so I need this query to fail. I don't know how to make that happen.

    Code:
    mysql (root in 'SomeDatabase'): select inv_num, hotel_num, inv_date, room_type_1 from Inventory where room_type_1 > 0 and inv_date >= '2016-02-01' and inv_date < '2016-02-07' and hotel_num = '77';
    +---------+-----------+------------+-------------+
    | inv_num | hotel_num | inv_date   | room_type_1 |
    +---------+-----------+------------+-------------+
    |     806 |        77 | 2016-02-06 |           5 |
    |     805 |        77 | 2016-02-05 |           5 |
    |     803 |        77 | 2016-02-03 |           5 |
    |     802 |        77 | 2016-02-02 |           5 |
    |     801 |        77 | 2016-02-01 |           5 |
    +---------+-----------+------------+-------------+
    5 rows in set (0.00 sec)
    Heres the schema:

    Code:
    mysql (root in 'SomeDatabase'): describe Inventory;
    +--------------------+--------------+------+-----+-------------------+-----------------------------+
    | Field              | Type         | Null | Key | Default           | Extra                       |
    +--------------------+--------------+------+-----+-------------------+-----------------------------+
    | inv_num            | int(14)      | NO   | PRI | NULL              | auto_increment              |
    | hotel_num          | int(8)       | YES  |     | NULL              |                             |
    | inv_date           | date         | YES  |     | 0000-00-00        |                             |
    | room_type_1        | int(5)       | YES  |     | NULL              |                             |
    | room_type_1_price  | decimal(6,2) | YES  |     | NULL              |                             |
    | timestamp          | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
    +--------------------+--------------+------+-----+-------------------+-----------------------------+
    28 rows in set (0.01 sec)
    Anyone have any idea?

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    what do you mean you want it to fail, due to that one date being missing?
    Dave

  3. #3
    Join Date
    Jul 2010
    Posts
    7
    Quote Originally Posted by dav1mo View Post
    what do you mean you want it to fail, due to that one date being missing?
    By fail I mean just come up empty. Like no records found.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    like dav1mo Im not certain what you are trying to achieve.
    but you could use the count with a date range to identify the number of rows there should be in a block.
    Im not sure how you will want to use this but you may need to use a sub select
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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