Exclude records inbetween two dates

[SupaMonkey]

Its been a long week and Im probably just not thinking properly! Basically, I have two tables:

create table dresses (
id int unsigned auto_increment primary key,
reference_number varchar(16)
);

create table bookings (
id int unsigned auto_increment primary key,
dress_id int unsigned,
booked_from date,
booked_to date,
reason varchar(32)
);

Now I can select everything from dresses with all my required criteria, but what I need done is that the select statement excludes any dresses that are within a specified time frame.

For arguments sake:
I need a dress from '2006-01-10' to '2006-01-14'. I want to see all the dresses in my database that arent currently booked out inbetween those two date.


So it would be something along the lines of:

SELECT
dresses.id,reference_number from dresses,bookings
WHERE
NOT (booked_from BETWEEN('2006-01-10') AND ('2006-01-14')) AND
NOT (booked_to BETWEEN('2006-01-10') AND ('2006-01-14'))
;

Problem comes in with the relationship in-between dresses and bookings clearly. How do I make it display all the dresses?

(Just a note, a dress does not necessarily have a booking at all - so something like "where bookings.dress_id=dresses.id would exclude that dress entirely instead of including it because there is no booking for it.)

Basically I just want to select all the dresses that are in the table while leaving out the dresses that are being booked on those dates :)

[obsidian]

i think you should be able to do something along these lines:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] dresses.id, reference_number [color=green]from[/color] [color=orange]dresses,[/color] bookings [color=green]WHERE[/color] dresses.id [color=orange]=[/color] dress_id [color=blue]AND[/color] dresses.id NOT [color=green]IN[/color] ([span style=\'color:blue;font-weight:bold\']SELECT[/span] dress_id [color=green]FROM[/color] [color=orange]bookings[/color] [color=green]WHERE[/color] (booked_[color=green]from[/color] [color=orange]BETWEEN[/color] [color=red]'2006[span style=\'color:orange\']-[/color]01[color=orange]-[/color]10'[/span] [color=blue]AND[/color] [color=red]'2006[span style=\'color:orange\']-[/color]01[color=orange]-[/color]14'[/span]) [color=blue]OR[/color] (booked_to BETWEEN [color=red]'2006[span style=\'color:orange\']-[/color]01[color=orange]-[/color]10'[/span] [color=blue]AND[/color] [color=red]'2006[span style=\'color:orange\']-[/color]01[color=orange]-[/color]14'[/span]));
[!--sql2--][/div][!--sql3--]

again, i'm not positive of your structure, but this gives you an idea of how to go about it.

[SupaMonkey]

Hi Obsidian,
I included my structure in my initial post for you guys.

I tried your way but what that does is return the items in bookings which are not in use in the timeframe.

I want it to return all the dresses that are not booked inbetween two dates. Not return all the booked items that are not booked for the timeframe.

Thats why I say "WHERE dresses.id=bookings.dress_id" wouldn't work because that would exclude all the dresses automatically because they dont have correlating records in the table 'bookings'.

[fenway]

Huh? But obsidian's query returns a dress.id -- isn't that what you want?

[SupaMonkey]

Contents of "Dresses":
[code]
mysql> select id,reference_number from dresses;
+----+------------------+
| id | reference_number |
+----+------------------+
|  9 | 1                |
|  8 | ABC34263250      |
| 10 | ABC0000          |
| 11 | ABC00001         |
| 12 | ABC00001         |
| 13 | ABC00001         |
| 14 | ABC00001         |
| 15 | ABC00001         |
| 16 | ABC00001         |
| 17 | ABC00001         |
| 18 | ABC00001         |
| 19 | ABC00001         |
| 20 | ABC00001         |
| 21 | ABC00001         |
+----+------------------+
[/code]

Contents of "Bookings":
[code]
mysql> select * from bookings;
+----+----------+-------------+------------+--------------+
| id | dress_id | booked_from | booked_to  | reason       |
+----+----------+-------------+------------+--------------+
|  1 |        8 | 2006-01-13  | 2006-01-13 | Dry Cleaners |
|  2 |        9 | 2006-02-27  | 2006-02-28 | Hired        |
+----+----------+-------------+------------+--------------+
[/code]

Obsidians Query & Result:
[code]
mysql> SELECT
    ->  dresses.id, reference_number,bookings.*
    -> FROM
    ->  dresses, bookings
    -> WHERE
    ->  dresses.id = dress_id AND dresses.id NOT IN
    ->  (SELECT dress_id FROM bookings WHERE (booked_from BETWEEN '2006-01-10' AND '2006-01-14') OR (booked_to BETWEEN '2006-01-10' AND '2006-01-14'));
+----+------------------+----+----------+-------------+------------+--------+
| id | reference_number | id | dress_id | booked_from | booked_to  | reason |
+----+------------------+----+----------+-------------+------------+--------+
|  9 | 1                |  2 |        9 | 2006-02-27  | 2006-02-28 | Hired  |
+----+------------------+----+----------+-------------+------------+--------+
[/code]

What I actually want to get:
[code]
mysql> "magical MySQL query";
+----+------------------+
| id | reference_number |
+----+------------------+
|  9 | 1                |
| 10 | ABC0000          |
| 11 | ABC00001         |
| 12 | ABC00001         |
| 13 | ABC00001         |
| 14 | ABC00001         |
| 15 | ABC00001         |
| 16 | ABC00001         |
| 17 | ABC00001         |
| 18 | ABC00001         |
| 19 | ABC00001         |
| 20 | ABC00001         |
| 21 | ABC00001         |
+----+------------------+
[/code]
(
Notice how:
"Dress ID 8" isnt listed because it would be booked out for the desired timeframe.
"Dress IDs 10-21" still show even though they have not been booked for anything.
)

Basically I want to get ALL dresses that arent going to be booked out within the time frame. What obsidians query does is returns all booked items that arent within that timeframe.
Two different results :)

Its sick, I know.
At the end of the day, all I can think of doing is two seperate queries one which selects the dresses (in a loop) and then one which checks if the dress is in the bookings table for that date. I would however much prefer having it all done in one query.

[SupaMonkey]

Just in case someone comes across the same problem, I think this solves it:

[code]
SELECT DISTINCT id,reference_number FROM dresses
  WHERE NOT EXISTS (SELECT * FROM bookings
                    WHERE (bookings.dress_id = dresses.id) AND
              (booked_from BETWEEN('2006-01-10') AND ('2006-01-14')) AND
              (booked_to BETWEEN('2006-01-10') AND ('2006-01-14'))
           );
[/code]

[wickning1]

Subqueries are almost always slower than joins. Here's the equivalent with a join:
[code]SELECT d.id, d.reference_number
FROM dresses d
LEFT JOIN bookings b
   ON d.id=b.dress_id
   AND b.booked_from BETWEEN('2006-01-10') AND ('2006-01-14')
   AND b.booked_to BETWEEN('2006-01-10') AND ('2006-01-14')
WHERE b.dress_id IS NULL[/code]

I'm not sure why you're using DISTINCT, neither of our queries should be returning any dress more than once, but you could add it to mine too if necessary.