Jump to content


Photo

Exclude records inbetween two dates


  • Please log in to reply
6 replies to this topic

#1 SupaMonkey

SupaMonkey
  • Members
  • PipPip
  • Member
  • 17 posts
  • LocationSouth Africa

Posted 14 February 2006 - 01:06 PM

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 :)
Website design, development & web hosting | 4x4 Electrical & Hydraulic Winches | Car Parts, Accessories, Styling, Lambo/Body/HID/Xenon Kits | Escorts, strippers & adult clubs directory | Adult Website design, development & web hosting

#2 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 14 February 2006 - 01:49 PM

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 from dresses, bookings WHERE dresses.id = dress_id AND dresses.id NOT IN ([span style=\'color:blue;font-weight:bold\']SELECT[/span] dress_id FROM bookings WHERE (booked_from BETWEEN '2006[span style=\'color:orange\']-01-10'[/span] AND '2006[span style=\'color:orange\']-01-14'[/span]) OR (booked_to BETWEEN '2006[span style=\'color:orange\']-01-10'[/span] AND '2006[span style=\'color:orange\']-01-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.
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 SupaMonkey

SupaMonkey
  • Members
  • PipPip
  • Member
  • 17 posts
  • LocationSouth Africa

Posted 14 February 2006 - 02:02 PM

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'.
Website design, development & web hosting | 4x4 Electrical & Hydraulic Winches | Car Parts, Accessories, Styling, Lambo/Body/HID/Xenon Kits | Escorts, strippers & adult clubs directory | Adult Website design, development & web hosting

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 14 February 2006 - 08:53 PM

Huh? But obsidian's query returns a dress.id -- isn't that what you want?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 SupaMonkey

SupaMonkey
  • Members
  • PipPip
  • Member
  • 17 posts
  • LocationSouth Africa

Posted 15 February 2006 - 08:11 AM

Contents of "Dresses":
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         |
+----+------------------+

Contents of "Bookings":
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        |
+----+----------+-------------+------------+--------------+

Obsidians Query & Result:
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  |
+----+------------------+----+----------+-------------+------------+--------+

What I actually want to get:
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         |
+----+------------------+
(
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.
Website design, development & web hosting | 4x4 Electrical & Hydraulic Winches | Car Parts, Accessories, Styling, Lambo/Body/HID/Xenon Kits | Escorts, strippers & adult clubs directory | Adult Website design, development & web hosting

#6 SupaMonkey

SupaMonkey
  • Members
  • PipPip
  • Member
  • 17 posts
  • LocationSouth Africa

Posted 15 February 2006 - 09:31 AM

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

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'))
           );

Website design, development & web hosting | 4x4 Electrical & Hydraulic Winches | Car Parts, Accessories, Styling, Lambo/Body/HID/Xenon Kits | Escorts, strippers & adult clubs directory | Adult Website design, development & web hosting

#7 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 15 February 2006 - 12:28 PM

Subqueries are almost always slower than joins. Here's the equivalent with a join:
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

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users