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

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