Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2006
    Posts
    18

    Unanswered: Simple join/query problem - I'm a newbie

    Hello All!

    I've done just your basic queries and have no problems with those, but now I find myself into a project that has two tables - with identical columns - that must be joined.

    Both tables (one named Occupancy, the other Reservations) have the following columns:

    CheckIn, CheckOut, TimeStamp, RoomNumber, BookingType, BookingAgent, ConfirmNum_FK, RoomCategory_FK

    When a user tries to make a reservation, I want to join the CheckIn, CheckOut, RoomNumber, RoomCategory columns from both tables to find out how many rooms have been booked between two dates submitted by a user.

    I've tried this query, but it's not working:

    SELECT CheckIn, CheckOut, RoomNumber, RoomCategory
    FROM occupancy, reservations
    WHERE CheckIn < '2006-12-02' AND CheckOut > '2006-12-26';

    The "testing" dates shown in the above query will be replaced with Vars, but I don't think that's the problem.

    Appreciate all assistance,

    Expatriate

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    Check out this article and also might need a redesign of your tables, you should not have a separate reservation/unoccupied tables.

  3. #3
    Join Date
    Apr 2006
    Posts
    18
    Quote Originally Posted by guelphdad
    Check out this article and also might need a redesign of your tables, you should not have a separate reservation/unoccupied tables.
    Guelphdad -

    Yes, I've agonized over this issue (one table or two) and ***thought*** that two made more sense but now I'm rethinking that (again) because I'd like to do counts on both occupied and reserved room types which I apparently can't do by Unioning both tables - which I found out how to do yesterday.

    So maybe the best way would be to create one table that would hold all checkin and checkout dates with a column that would indicated which are occupied and which are reserved, a FK column referring to room cateogry, and another FK column referring to the room number that belongs to that category?

    I've struggled to get this design normalized - to some degree - but I become so very confused sometimes as I'm so new to this. I'm an expat working alone from the Philippines with no one here that can show me the error(s) of my ways.

    I did read your article and that began to open my eyes.

    Would you be willing to look at my DB design so I may get your opinion on what's good and what's just wrong and shold be fixed?

    Expatriate

Posting Permissions

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