|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!
|
|
#1
June 29th, 2003, 10:50 AM
|
||||
|
||||
Avoiding partial extraction while joining
Hi there.
This is the situation: Table A with a set of bookable resources (rooms, cars... it doesn't matter what). Table B with reservations with an obvious foreign key to A and 2 timestamp for starttime and endtime of the reservation. We need to extract all available resources in a given time interval. Lazy way: query over B to get all unavailable resources and a second query over A to get all the other resources. Smart way: one only query over A joined to B. But... I'm not so smart!  I don't find a way to extract all available resources that don't figure in the table B (read: never-booked-before resources).Have someone out there an idea to show please? Thanks in advance. Bye
|
|
#2
June 29th, 2003, 11:20 AM
|
|||
|
|||
|
Code:
select * from a where booking not in (select booking from b where localtime between starttime and endtime)
|
|
#3
June 29th, 2003, 12:01 PM
|
||||
|
||||
|
In MySQL subqueries will only be supported since version 4.1 on.
Even if we know that version is about to come, I intended to create a general query useful over every DB engine. Have you any other idea?
|
|
#4
June 29th, 2003, 02:51 PM
|
||||
|
||||
|
Quote:
I hope it's ok (i tried it in a visual basic project and it seems to work... )SELECT a.* FROM A a LEFT JOIN B b ON (a.booking=b.booking) WHERE b.starttime IS NULL OR localtime NOT BETWEEN b.starttime AND b.endtime Honestly i don't know why the "b.starttime IS NULL" clause permits the query to work...but it doesn't matter, if it really works, doesn't it? 
|
|
| Viewing: Dev Shed Forums > Databases > Database Management > Avoiding partial extraction while joining |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
