Forum Moderators: open
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.
Thanks to all in advance,
Neophyte
Well, it could be - geez, I'm really showing my ignorance. I really don't know which is the proper way to do it as I've never done a join - or a union. My learning level is simply the very most basic (i.e. select * from table where columnName = x)
From the little reading I've done on joins, I thought that if you want to "combine" all or specific columns from two or more tables, it had to be accomplished with a "join".
Essentially, the task is to get all data from the 4 columns mentioned based upon the two dates (checkin and checkout) which will be specified by a user - I've just put in hard-wired dates for testing.
Obviously I'm just scratching the surace with this kind of query and - quite obviously - am feeling my way in the dark.
Based upon what I need to do, can you suggest a query (join/union/whaterver) that will get this task done?
Neophyte
SELECT CheckIn, CheckOut, RoomNumber, RoomCategory
FROM occupancy
UNION
SELECT CheckIn, CheckOut, RoomNumber, RoomCategory
FROM reservations
WHERE CheckIn < '2006-12-02' AND CheckOut > '2006-12-26';
That works perfectly. Thank you!
Neophyte
SELECT CheckIn, CheckOut, RoomNumber, RoomCategory
FROM occupancy
UNION
SELECT CheckIn, CheckOut, RoomNumber, RoomCategory
FROM reservations
WHERE CheckIn < '2006-12-02' AND CheckOut > '2006-12-26';
Are you sure this works as you expect?
The WHERE caluse will only apply to the reservations table and ALL occupancy records will be returned.
You may need this instead:
SELECT CheckIn, CheckOut, RoomNumber, RoomCategory
FROM occupancy
WHERE CheckIn < '2006-12-02' AND CheckOut > '2006-12-26';
UNION
SELECT CheckIn, CheckOut, RoomNumber, RoomCategory
FROM reservations
WHERE CheckIn < '2006-12-02' AND CheckOut > '2006-12-26';
Hummm - I've tried it both ways, and it works the same which is interesting and scary at the same time:
*****
SELECT CheckIn, CheckOut, RoomNumber, RoomCategory
FROM occupancy
UNION
SELECT CheckIn, CheckOut, RoomNumber, RoomCategory
FROM reservations
WHERE CheckIn < '2006-12-02' AND CheckOut > '2006-12-26'; **Where Clause**
*****
SELECT CheckIn, CheckOut, RoomNumber, RoomCategory
FROM occupancy
WHERE CheckIn < '2006-12-02' AND CheckOut > '2006-12-26'; **Where Clause**
UNION
SELECT CheckIn, CheckOut, RoomNumber, RoomCategory
FROM reservations;
*****
SELECT CheckIn, CheckOut, RoomNumber, RoomCategory
FROM occupancy
WHERE CheckIn < '2006-12-02' AND CheckOut > '2006-12-26'; **Where Clause**
UNION
SELECT CheckIn, CheckOut, RoomNumber, RoomCategory
FROM reservations
WHERE CheckIn < '2006-12-02' AND CheckOut > '2006-12-26'; **Where Clause**
*****
All three queries above yield the same result. Should that be correct? Or have I got things really mucked up?
Neophyte
for example:
WHERE CheckIn < '2006-12-02' AND CheckOut > '2006-12-26';
I'm not even sure if this is the correct syntax for date range checks, but suppose it is, then adding this clause will only change the returned results if you have records that are OUTSIDE the range - i.e. with CheckIn dates AFTER 2006-12-02 and CheckOut dates BEFORE 2006-12-26
