1

I'm new to MySQL, I'm trying to fetch a count of records of one table to another table. I've two tables one is property and another is rooms. I'm trying to show count of rooms for each property. I've passed the primary key of property table to rooms table.

Now in my property table I want to show a column of roomCount for each property.

I'm filtering the property table on the basis of area and cluster.

here is my SP for property records:

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

Now when I'm trying to show a count of rooms, I'm not getting proper result. It shows only properties that have rooms and properties with no rooms are not shown in result table.

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

2 Answers 2

1

The "standard" INNER JOIN only returns records that match in both tables. To return records that only exist in the left or right table you need to use an OUTER JOIN. This can be a LEFT JOIN or a RIGHT JOIN depending on whether you want to retrieve all the records from the left or right table; by convention LEFT is more frequently used.

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

In the above you are selecting from tbl_rooms as the first table, and then doing one inner and then several LEFT outer joins on the other tables. So you are only going to get results from tbl_rooms, that is the LEFT table, that is how it works. You need to either move to using RIGHT outer joins on the other tables, or, and I recommend as probably less complicated to conceptualize, to start with the tbl_property table on the top/LEFT that you want to get all the results from.

So you need to start with FROM tbl_property at the top and then do your left joins down to LEFT JOIN tbl_rooms at the end, reverse the existing order. You'll then get all the results from the table you start with, and only the results that match from the table you are LEFT outer joining on.

EDIT: Something like this should give you properties with a room count:

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;

Note we start with tbl_properties and then LEFT JOIN to tbl_rooms.

7
  • I'm just trying to fetch count of rooms for each property (SELECT COUNT(room_id) FROM tbl_rooms WHERE room_property_id = 1; gives COUNT(room_id): 0 and SELECT COUNT(room_id) FROM tbl_rooms WHERE room_property_id = 2; gives COUNT(room_id): 1). But I'm not able to send parameter value because I'm sending other parameters area and cluster ID's for filtering data. Dec 18, 2017 at 5:45
  • Is it possible to one SP in Another SP ? Dec 18, 2017 at 5:47
  • @GaneshAher I'm responding to your "It shows only properties that have rooms and properties with no rooms are not shown in result table." It shows only properties that have rooms because you are starting on the LEFT from tbl_rooms. Do you understand that?
    – Ivan McA
    Dec 18, 2017 at 5:48
  • Yes I got that, and I made a changes as per that but not getting required result. Dec 18, 2017 at 5:51
  • I added an example that should give you properties with a room count, listing all properties even if they have no rooms. Does that help, or is your question something else?
    – Ivan McA
    Dec 18, 2017 at 5:58
0

I got a solution for problem. I'm just use sub query to get expected result.

I've done like this:

(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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.