All rows issue

Database
Enthusiast

All rows issue

Hi folks,
need some help on this.

To get all the days for all the hotel_id i am doing the following query.

select
hotel_id,
hotel_date,
week_id
mth_id,
from hotel_prod, calendar_prod

This gives me hotel_id for each day in the calendar year..

now i have another table which gives me the following columns.
table name is Hotel_room_reservation_details
hotel_id, room_type, given_date,

it has data like this
54 suite 10/04
54 hall 10/05
54 hall 10/05
54 suite 10/05
66 suite 10/05
33 room 10/05
44 suite 10/07

Now In my result I want a count for each hotel_id for each room type for each day.

54 suite 10/04 1
54 hall 10/04 0
54 room 10/04 0
66 suite 10/04 0
66 hall 10/04 0
66 room 10/04 0
33 suite 10/04 0
33 hall 10/04 0
33 room 10/04 0
44 suite 10/04 0
44 hall 10/04 0
44 room 10/04 0
54 suite 10/05 1
54 hall 10/05 2
54 room 10/05 0
66 suite 10/05 1
66 hall 10/05 0
66 room 10/05 0

hope this makes it easy to put up the query
thanks in advance

1 REPLY
Junior Contributor

Re: All rows issue

You probably need another cross join to a table containing the different room types followed by a left join to the details:

select
hotel_id,
hotel_date,
week_id
mth_id,
coalesce(cnt,0)
from hotel_prod hp cross join calendar_prod cp cross join room_types rt
left join
(select hotel_id, room_type, given_date, count(*) as cnt
from Hotel_room_reservation_details
group by 1,2,3
) dt
on hp.hotel_id = dt.hotel_id
and hp.room_type = dt.room_type
and cp.hotel_date =dt.given_date

Dieter