Mysql – How to show count of records from one table to another table

join;MySQLstored-procedures

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

Best Answer

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.

Related Solutions

Sql-server – SQL join query to show rows with non-existent rows in one table

Thank you for SQLfiddle and sample data! I wish more questions started this way.

If you want all members regardless of whether they have an entry for that date, you want a LEFT OUTER JOIN. You were very close with this version however a little trick with outer joins is that if you add a filter to the outer table in the WHERE clause, you turn an outer join to an inner join, because it will exclude any rows that are NULL on that side (because it doesn't know if NULL would match the filter or not).

I modified the first query to get a row for every member:

SELECT Members.Member_ID
      ,Time_Entry.Date_Start
      ,Time_Entry.Hours_Actual
      ,Time_Entry.Hours_Bill
FROM dbo.Members
  LEFT OUTER JOIN dbo.Time_Entry
--^^^^ changed from FULL to LEFT
  ON Members.Member_ID = Time_Entry.Member_ID
  AND Time_Entry.Date_Start = '20131110';
--^^^ changed from WHERE to AND

I'll leave it as an exercise for the reader to take it from there and add the other columns, formatting, COALESCE etc.

Some other notes:

please always use the schema prefix when creating and referencing objects
please always use a length when converting to varchar etc.
stay away from ambiguous, regional date formats like mm-dd-yyyy

consider using aliases to make your queries easier to read. E.g. the above could be re-written as:

SELECT m.Member_ID
  ,t.Date_Start
  ,t.Hours_Actual
  ,t.Hours_Bill
FROM dbo.Members AS m
LEFT OUTER JOIN dbo.Time_Entry AS t
ON m.Member_ID = t.Member_ID
AND t.Date_Start = '20131110';

... a lot tidier, IMHO, as long as you use sensible aliases.

Mysql – Query multiple rooms with different conditionals (Adults, Children)

first insert the resultset of the room_type table to a temp table with the filtering by your parameters

 r.max_adults >= 3 AND r.max_children >= 0

then query that table and join the property and plan tables to it and it should be faster.

Best Answer

Related Solutions

Sql-server – SQL join query to show rows with non-existent rows in one table

Mysql – Query multiple rooms with different conditionals (Adults, Children)

Related Question