SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast amit1101's Avatar
    Join Date
    May 2003
    Location
    London
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Query help for Hotel search

    Hi all,

    Wondering if you can help? I am currently working on a travel website, which allows users to search hotels. Im using a MS SQL database along with classic ASP.

    I would like to search by Area, Class and/or Hotel facility. I have set up the tables and an SQL query, but it doesnt seem to work? For example I want to search for a hotel in a certian area, with a 3 star rating and has a
    Restaurant (FacilityID = 1) AND
    Gym (FacilityID = 2) AND
    Swimming pool (FacilityID = 3) AND
    Transfer Service (FacilityID = 4) AND
    SPA (FacilityID = 5)

    I know that I have a hotel in the database with the above criteria but it doesnt pull out the result?

    The query I have used it as follows:

    SELECT Hotel.HotelID, Hotel.HotelName, Hotel.HotelAddress, Hotel.HotelDescription, Hotel.HotelClass, Hotel.HotelCapacity FROM Hotel, Facility, HotelFacility WHERE Hotel.AreaID = '6' AND Hotel.HotelClass = '3' AND Hotel.HotelID = HotelFacility.HotelID AND Facility.FacilityID = HotelFacility.FacilityID AND HotelFacility.FacilityID = '1' AND HotelFacility.FacilityID = '2' AND HotelFacility.FacilityID = '3' AND HotelFacility.FacilityID = '4' AND HotelFacility.FacilityID = '5' ORDER BY Hotel.HotelName ASC, Hotel.HotelClass ASC

    I have set up the tables as follows:

    Hotel - Holds information about the hotel, i.e. location, name, rating etc
    ------
    HotelID - Primary Key
    AreaID - Foreign Key
    HotelName
    HotelClass
    etc etc

    Area - The area in which the hotel could be
    -------
    AreaID - Primary Key
    AreaName

    Facility - A facility such as restaurant, spa, gym, hotel transfer etc...
    --------
    FacilityID - Primary Key
    FacilityName

    HotelFacility - Links the facility and hotel tables, so that one hotel can have more than one facility.
    -----------
    HotelFacilityID
    HotelID - Foreign Key
    FacilityID - Foreign Key

    Any ideas would be grately appreciated!

    Amit

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    select Hotel.HotelID
         , Hotel.HotelName
         , Hotel.HotelAddress
         , Hotel.HotelDescription
         , Hotel.HotelClass
         , Hotel.HotelCapacity 
      from Hotel
    inner
      join (
           select HotelID
             from HotelFacility 
            where FacilityID in (1,2,3,4,5)
           group
               by HotelID
           having count(*) = 5
           ) as all5
        on all5.HotelID = Hotel.HotelID    
     where Hotel.AreaID = '6' 
       and Hotel.HotelClass = '3'
    order 
        by Hotel.HotelName asc
         , Hotel.HotelClass asc
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jul 2006
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try the query inside an winsql utility to be sure that the query is correct.

  4. #4
    SitePoint Enthusiast amit1101's Avatar
    Join Date
    May 2003
    Location
    London
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937, that worked!

    Any chance you could explain where I had gone wrong?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    sure

    WHERE clauses are evaluated one row at a time

    each column's value is a fixed, single value

    thus each value of HotelFacility.FacilityID cannot be 1 and 2 and 3 and 4 and 5 at the same time
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast amit1101's Avatar
    Join Date
    May 2003
    Location
    London
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Ahhhh!!! I see, D'OH!

    Well thank you very much for the answer and the explanation! It is very much appreciated!

  7. #7
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    Out of curiousity. Where is the hotel database hosted? are you querying the hotel daytabase from your own site like an agent? how are you processing payment

    thank you


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
  •