[Careercup] 15.2 renting Apartment II Rental II

Write a SQL query to get a list of all buildings and the number of open requests (requests in which status equals ' open ').

--TABLE Apartments

+-------+------------+------------+| Aptid| Unitnumber| Buildingid|+-------+------------+------------+|101| A1|1||102| A2|2|| 103 | A3 | 3 || Span style= "color: #800000; Font-weight:bold; " >201 | B1 | 4 || Span style= "color: #800000; Font-weight:bold; " >202 | B2 | 5 |-------+------------+------------+     

--TABLE buildings

+------------+-----------+---------------+---------------+| Buildingid| Complexid| Buildingname| Address|+------------+-----------+---------------+---------------+|1|11| Eastern Hills| San Diego, CA||2|12| EastEnd| Seattle, WA||3|13| North Park | New York || 4 | 14 | South Lake | Orlando, FL || 5 | 15 | West Forest | Atlanta, GA | +------------+-----------+---------------+--------- ------+ 

--TABLE Tenants

+----------+------------+| TenantID| Tenantname|+----------+------------+1000 | Zhang San || 1001 | Li Si || 1002 | Wang Wu || 1003 | Yang Liu |----------+------------+      

--TABLE complexes

+-----------+---------------+| Complexid| Complexname|+-----------+---------------+|11| Luxuary World ||  | Paradise ||  | Woderland ||  | Dreamland ||  | Lostparis |  +-----------+---------------+ 

--TABLE Apttenants

+----------+-------+| TenantID| Aptid|+----------+-------+|1000|102||1001|102||1002| 101 || Span style= "color: #800000; Font-weight:bold; " >1002 | 103 || Span style= "color: #800000; Font-weight:bold; " >1002 | 201 || Span style= "color: #800000; Font-weight:bold; " >1003 | 202 |----------+-------+      

--TABLE Requests

+-----------+--------+-------+-------------+| RequestID| Status| Aptid| Description|+-----------+--------+-------+-------------+|50|Open|101|||60|Close|103|||70|Close|102| | |  | Open | 201 | | |  | Open | 202 | | +-----------+--------+-------+-------------+ 

This question lets us return all the building, and marks out how many open requests each building has, so we first calculate the number of open request for each building, The corresponding Buildingname is then returned in conjunction with the buildings table, as the requests tables correspond to apartment and request, and a building may have many apartment, So we're going to combine the apartments table and the requests table to calculate the number of open requests for each building, and we'll use the intra-inner join to do the Aptid table and apartments table through the requests column, Then, by Buildingid to the group and generating a column named Count, and then left-handed with the buildings table and the Count column, it is important to note that if a building does not have an open request, then we need to return 0, that is, we need to change NULL to 0. In MySQL we use the ifnull function, while SQL Server uses the isnull,oracle NVL, detailed comparisons can be found here. See the code below:

SELECTBuildingname, Ifnull (Count,0) as 'Count'  fromBuildings Left JOIN(SELECTApartments.buildingid,COUNT(*) as 'Count'  fromRequestsINNER JOINApartments onRequests.aptid=Apartments.aptidWHERERequests.status= 'Open' GROUP  byapartments.buildingid) reqcounts onReqcounts.buildingid=Buildings.buildingid;

Operation Result:

+---------------+-------+|Buildingname| Count |+---------------+-------+|Eastern Hills|     1 ||EastEnd      |     0 ||North Park|     0 ||South Lake|     1 ||West Forest|     1 |+---------------+-------+

Careercup all in one topic summary

[Careercup] 15.2 renting Apartment II Rental II