Join two tables with group

Question

I need help with a query joining two tables for a hostel (dorm) database:

Rooms - room id, category, rent, no of beds, etc

Hosteler - hosteler id, name, room id, etc

I need to generate a result set giving all the rooms, total no of beds and occupied no. of beds and free no of beds, etc

Here is the query I am using:

SELECT r.R_ID , r.R_Beds, count(h.h_id), (r.r_beds-count(h.h_id)) ,
if((r.r_beds-count(h.h_id))>0, 'Available', 'Full' ) , r.r_rent
FROM T_Rooms r LEFT OUTER JOIN t_hostelers h
ON r.r_id = h.h_roomno
WHERE h.h_status= 'active'
GROUP BY h.h_roomno

But I am getting only rows from rooms table which have a value in hostelers table. I need to show other rooms with no occupancy also.

Please help me find out my oversight or mistake.

xQbert xQbert · Accepted Answer · 2011-12-19T17:57:38

Option 1

SELECT r.R_ID , r.R_Beds, count(h.h_id), (r.r_beds-count(h.h_id)) ,
if((r.r_beds-count(h.h_id))>0, 'Available', 'Full' ) , r.r_rent
FROM T_Rooms r 
LEFT OUTER JOIN t_hostelers h
  ON r.r_id = h.h_roomno
     AND h.h_status= 'active'
GROUP BY h.h_roomno

Option 2

SELECT r.R_ID , r.R_Beds, count(h.h_id), (r.r_beds-count(h.h_id)) ,
if((r.r_beds-count(h.h_id))>0, 'Available', 'Full' ) , r.r_rent
FROM T_Rooms r 
LEFT OUTER JOIN t_hostelers h
  ON r.r_id = h.h_roomno
WHERE
     (h.h_status= 'active' or H.H_Status is null)
GROUP BY h.h_roomno

The problem is your where clause is excluding records from t_rooms because there is no record in t_hostelers. To correct you need to apply the filter on the join so it only applies to the t_hostelers OR include NULL results in a where clause.

Join two tables with group

1 Answers