SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    UK
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Select question - Getting results from 2 tables at once

    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by SQHell
    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
    Code:
    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 )
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    UK
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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 --
    Code:
    PropID  Booking  StartDate   EndDate
      1       123    2005-03-01  2005-03-11
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    UK
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I used this solution to my original problem of creating a reservation system.

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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,182
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  8. #8
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    UK
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  9. #9
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    UK
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    assume that existing bookings have startdate and enddate, and the client provides the date range desired as @request_start and @request_end
    Code:
    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?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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"
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    UK
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, so are they just values eg. 02-02-2005 ? I have never seen the @ used before like this.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yeah, sorry, they were intended to be script variables

    maybe i shoulda wrote $request_start and $request_end

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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