SQL Select question - Getting results from 2 tables at once

[SQHell]

I have 2 tables. 1 for properties and 1 for dates. The dates table has a list of dates for when a property is booked and therefore not available for rent.

I would like users to be able to select a size of property (number of bedrooms) and the dates they want to stay.

I have never worked out how to use SQL Joins and I suspect it is needed now. Also I have to display the properties that are not booked (obviously!) is there an SQL statement where the value doesn't exist?

So for example select 2 bedroom properties where dates aren't in a date range. The users dates I suspect would need to be in an array how can I compare those with the value in the tables?

[r937]

So for example select 2 bedroom properties where dates aren't in a date range.

assume that existing bookings have startdate and enddate, and the client provides the date range desired as @request_start and @request_end

select id
     , name
  from properties 
 where bedrooms = 2
   and not exists
       ( select 1 from bookings 
          where property_id = properties.id
            and @request_start <= enddate    
            and @request_end >= startdate )

[SQHell]

Hi,

Thanks for the reply. The start date and end date is to be given by a drop down table on a form. The only dates in the table are for times when the property is booked, therefore if the dates aren't in the table the property is available.

The reservation table looks like this

PropID | Date | Booking ref
1 2005-03-11 123
1 2005-03-10 123
1 2005-03-09 123
1 2005-03-08 123
1 2005-03-07 123
1 2005-03-06 123
1 2005-03-05 123
1 2005-03-04 123
1 2005-03-03 123
1 2005-03-02 123
1 2005-03-01 123

[r937]

why do you have 11 rows for booking 123?

most reservation systems will have start and end dates for each booking

so your data would look like this --

PropID  Booking  StartDate   EndDate
  1       123    2005-03-01  2005-03-11

[SQHell]

I used this solution to my original problem of creating a reservation system.

http://www.sitepoint.com/forums/showthread.php?t=186236

[r937]

okay, i understand

in that case, you should be using the sql examples given in that thread

however, i disagree that the one-row-per-date design is better than the one-row-per-booking

for example, what's the sql for this: "which customer has booked the honeymoon suite on may 15th, and what are his start and end dates?"

perhaps it is not necessary to examine all the different queries and how easy/tough they will be in either database design

i mean, we could construct hypothetical situations all afternoon, some of which might never come up in your application

so the real decision on which database design you use should be up to you

all i can say is that i don't like the one in that other thread

[Toly]

I agree with you, Rudy. I don't see the logic of using a one-row-per-date design, unless it was combined with some sort of activity for each day.

[SQHell]

Thanks for the comments guys, seriously. Its good to hear constructive comments like this.

I think I would be best keeping the design for the time being. So going back to Rudy's first answer. What are @request_start and @request_end?

[SQHell]

assume that existing bookings have startdate and enddate, and the client provides the date range desired as @request_start and @request_end

select id
     , name
  from properties 
 where bedrooms = 2
   and not exists
       ( select 1 from bookings 
          where property_id = properties.id
            and @request_start <= enddate    
            and @request_end >= startdate )

Rudi - Going back to yuor example. Why have you got select 1? Also what are @request_start and @request_end?

[r937]

in an EXISTS subquery, it doesn't matter what you select

often, you'll see SELECT * or SELECT NULL, sometimes i'll use SELECT 937 just for fun

as for those fields, "[assume] the client provides the date range desired as @request_start and @request_end"

[SQHell]

Thanks, so are they just values eg. 02-02-2005 ? I have never seen the @ used before like this.

[r937]

yeah, sorry, they were intended to be script variables

maybe i shoulda wrote $request_start and $request_end