如何顯示從一個表到另一個表的記錄數？

我是 MySQL 新手，我正在嘗試將一個表的記錄計數獲取到另一個表。我有兩張桌子，一張是財產，另一張是房間。我正在嘗試顯示每個屬性的房間數。我已將屬性表的主鍵傳遞給房間表。

現在在我的屬性表中，我想roomCount為每個屬性顯示一列。

我正在根據area和過濾屬性表cluster。

這是我的財產記錄SP：

BEGIN
IF area_id != 0
     AND  cluster_id != 0 THEN 
SELECT  `property_id`, `property_code`, `property_added_date`,
       `property_updated_date`, `property_termination_date`,
       `property_ASYS_no`, `property_address_1`, `property_address_2`,
       `property_address_3`, `property_city`, tbl_cluster.cluster_name,
       `property_area`, tbl_area.area_name, `property_postcode`
   FROM  `tbl_property`
   INNER JOIN  tbl_cluster  ON tbl_property.property_cluster =tbl_cluster.cluster_id
   LEFT OUTER JOIN  tbl_area  ON tbl_property.property_area = tbl_area.area_id
   LEFT OUTER JOIN  tbl_landlord  ON tbl_property.property_landlord_id =tbl_landlord.landlord_id
   WHERE  tbl_property.property_area=area_id
     AND  tbl_property.property_cluster=cluster_id
   ORDER BY  `property_code` DESC;
END IF;
IF area_id != 0
     AND  cluster_id = 0 THEN 
SELECT  `property_id`, `property_code`, `property_added_date`,
       `property_updated_date`, `property_termination_date`,
       `property_ASYS_no`, `property_address_1`, `property_address_2`,
       `property_address_3`, `property_city`, tbl_cluster.cluster_name,
       `property_area`, tbl_area.area_name, `property_postcode`
   FROM  `tbl_property`
   INNER JOIN  tbl_cluster  ON tbl_property.property_cluster =tbl_cluster.cluster_id
   LEFT OUTER JOIN  tbl_area  ON tbl_property.property_area = tbl_area.area_id
   LEFT OUTER JOIN  tbl_landlord  ON tbl_property.property_landlord_id =tbl_landlord.landlord_id
   WHERE  tbl_property.property_area=area_id
   ORDER BY  `property_code` DESC;
END IF;
IF area_id = 0
     AND  cluster_id != 0 THEN 
SELECT  `property_id`, `property_code`, `property_added_date`,
       `property_updated_date`, `property_termination_date`,
       `property_ASYS_no`, `property_address_1`, `property_address_2`,
       `property_address_3`, `property_city`, tbl_cluster.cluster_name,
       `property_area`, tbl_area.area_name, `property_postcode`
   FROM  `tbl_property`
   INNER JOIN  tbl_cluster  ON tbl_property.property_cluster =tbl_cluster.cluster_id
   LEFT OUTER JOIN  tbl_area  ON tbl_property.property_area = tbl_area.area_id
   LEFT OUTER JOIN  tbl_landlord  ON tbl_property.property_landlord_id =tbl_landlord.landlord_id
   WHERE  tbl_property.property_cluster=cluster_id
   ORDER BY  `property_code` DESC;
END IF;
IF area_id = 0
     AND  cluster_id = 0 THEN 
SELECT  `property_id`, `property_code`, `property_added_date`,
       `property_updated_date`, `property_termination_date`,
       `property_ASYS_no`, `property_address_1`, `property_address_2`,
       `property_address_3`, `property_city`, tbl_cluster.cluster_name,
       `property_area`, tbl_area.area_name, `property_postcode`
   FROM  `tbl_property`
   INNER JOIN  tbl_cluster  ON tbl_property.property_cluster =tbl_cluster.cluster_id
   LEFT OUTER JOIN  tbl_area  ON tbl_property.property_area = tbl_area.area_id
   LEFT OUTER JOIN  tbl_landlord  ON tbl_property.property_landlord_id =tbl_landlord.landlord_id
   ORDER BY  `property_code` DESC;
END IF;
END

現在，當我試圖顯示房間數時，我沒有得到正確的結果。它僅顯示有房間的屬性，沒有房間的屬性不會顯示在結果表中。

my rooms table:

BEGIN
SELECT  tbl_rooms.room_id, `room_id` `user_id`, `room_landlord_id`,
       tbl_property.property_id, tbl_rooms.room_property_id,
       `room_custom_number`, `room_name`
   FROM  `tbl_rooms`
   INNER JOIN  tbl_room_type  ON tbl_rooms.room_type =tbl_room_type.room_type_id
   LEFT OUTER JOIN  tbl_yes_no_type AS fire_escapeYN  ON tbl_rooms.room_fire_escape = fire_escapeYN.type_id
   LEFT OUTER JOIN  tbl_yes_no_type AS fire_doorsYN  ON tbl_rooms.room_fire_doors =fire_doorsYN.type_id
   LEFT OUTER JOIN  tbl_bedroom_type  ON tbl_rooms.room_bedroom_type =tbl_bedroom_type.bedroom_id
   LEFT OUTER JOIN  tbl_yes_no_type AS room_wcYN  ON tbl_rooms.room_wc =room_wcYN.type_id
   LEFT OUTER JOIN  tbl_yes_no_type AS room_sinkYN  ON tbl_rooms.room_sink =room_sinkYN.type_id
   LEFT OUTER JOIN  tbl_yes_no_type AS room_showerYN  ON tbl_rooms.room_shower =room_showerYN.type_id
   LEFT OUTER JOIN  tbl_yes_no_type AS room_extractor_fanYN  ON tbl_rooms.room_extractor_fan =room_extractor_fanYN.type_id
   LEFT OUTER JOIN  tbl_property  ON tbl_rooms.room_property_id =tbl_property.property_id
   WHERE  tbl_rooms.room_property_id=id;
END

“標準”INNER JOIN僅返回兩個表中匹配的記錄。要返回僅存在於左表或右表中的記錄，您需要使用OUTER JOIN. 這可以是 aLEFT JOIN或 a ，RIGHT JOIN具體取決於您是要從左表還是右表中檢索所有記錄；按照慣例，LEFT 更常用。

https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join https://www.w3schools.com/sql/sql_join_left.asp

在上面，您從tbl_rooms第一個表中選擇，然後在其他表上執行一個內部聯接，然後執行幾個 LEFT 外部聯接。所以你只會從 tbl_rooms 得到結果，也就是 LEFT 表，它就是這樣工作的。您需要轉而在其他表上使用 RIGHT 外連接，或者，我建議可能不太複雜的概念化，tbl_property從要從中獲取所有結果的頂部/左側的表開始。

因此，您需要從FROM tbl_property頂部開始，然後將左連接向下到LEFT JOIN tbl_rooms最後，顛倒現有順序。然後，您將從您開始使用的表中獲取所有結果，並且僅從您在左外部加入的表中獲取匹配的結果。

編輯：這樣的事情應該給你的房間計數屬性：

SELECT p.property_id, p.property_code, count(r.room_id) AS roomcount FROM tbl_properties p LEFT JOIN tbl_rooms r ON r.room_property_id = p.property_id GROUP BY p.property_id, p.propertycode;

請注意，我們從 開始，tbl_properties然後LEFT JOIN到tbl_rooms。

我得到了問題的解決方案。我只是使用子查詢來獲得預期的結果。

我已經這樣做了：

(SELECT 
   COUNT(room_property_id) 
FROM tbl_rooms 
   WHERE room_property_id = tbl_property.property_id) AS rooms, 

(SELECT 
   COUNT(defect_property_id) 
FROM tbl_defects 
   WHERE defect_property_id = tbl_property.property_id) AS defects 

FROM `tbl_property` 
   LEFT OUTER JOIN tbl_rooms ON tbl_rooms.room_property_id = tbl_property.property_id 
   LEFT OUTER JOIN tbl_defects ON tbl_defects.defect_property_id = tbl_property.property_id

引用自：https://dba.stackexchange.com/questions/193370