Outer join Issue

Database
Enthusiast

Outer join Issue

hi folks, I am stuck with a typical right outer join issue.

Here is my query. I am trying to insert data into a table.
Insert Into table XYZ

(
Select
Hotel_id,
month_id,
week_id,
romm_reserve_dt

from
hotel_tbl
)a
Right Join
(Select month_id,week_id, date_hotel from Hotel_Calendar) b
ON a.romm_reserve_dt = b.date_hotel

I have table that has room reservation information. The problem is that the room reservation information comes in only when the room is reserved in the hotel.(hotel_tbl)
if in a week that hotel has no rooms reserved in the table then i cannot see that hotel in this table(hotel_tbl).

I get the following results.

Hotel_ID Month_ID Week_id reservation_dt
Null 1 2009011
Null 1 2009012
hot_34 1 2009013 1/17/2009
hot_34 1 2009013 1/18/2009
Null 1 2009014
Null 2 2009021
Null 2 2009021

I need to get

Hotel_ID Month_ID Week_id reservation_dt
hot_34 1 2009011
hot_34 1 2009012
hot_34 1 2009013 1/17/2009
hot_34 1 2009013 1/18/2009
hot_34 1 2009014
hot_34 2 2009021
hot_34 2 2009021

So that i can get sums for each week_id for each hotel by counting the reservation_dt.

I am able to get all week_id with the outer join.But i am not able to associate it to a hotel_id.
there is another table that has hotel info and there is only one row for each hotel_id in this table. (hotel_data_tbl) Even this has hotel_ID

Can any one help me solve this issue.
6 REPLIES
Enthusiast

Re: Outer join Issue

Hi,

Can you try, something like this, to get ur output ( i have hard-coded the hotel_id value as 'hot_34' ) :

Insert Into table XYZ

(
Select
'hot_34',
month_id,
week_id,
romm_reserve_dt

from
hotel_tbl
)a
Right Join
(Select month_id,week_id, date_hotel from Hotel_Calendar) b
ON a.romm_reserve_dt = b.date_hotel

With Regards,
Raja
sunil.dexter@gmail.com
Enthusiast

Re: Outer join Issue

No I cannot,I have only listed one hotel for the example but there are numerous hotels in the table
Enthusiast

Re: Outer join Issue

Can any one help me with this...
Is there a replace function in teradata with which i can replace it with the original hotel_id where reservation date is not null for that particular month_id for all week_id.

Please help
Enthusiast

Re: Outer join Issue

Or can any one help me write a query that will bring back hotel_id for all the week_id and month_id. instead of null
Enthusiast

Re: Outer join Issue

Select HTLD.Hotel_Id
, HCAL.Month_Id
, HCAL.Week_Id
, HTL.Room_Reserve_Dt AS Reservation_Dt

From Hotel_Data_Tbl HTLD
Cross Join Hotel_Calendar HCAL
Left Join Hotel_Tbl HTL
On HTLD.Hotel_Id = HTL.Hotel_Id
And HTL.Room_Reserve_Dt = HCAL.Date_Hotel
;

If there are hotels in Hotel_Data_Tbl with no reservations and you do not want them,
make HTLD a select distinct of Hotel_Id from Hotel_Tbl.

HTH
Enthusiast

Re: Outer join Issue

thanks a lot Sir, I was able to resolve the issue