MySQL Left Outer Join with Count from joined table, Show all records
i am trying to create a query where there is a count of related records from another table. I'd like the "paren开发者_Python百科t" records whether there are related records (a count) or not.
SELECT r.region_name, r.region_id, r.abbreviation, r.map_order,
(IFNULL( COUNT( p.property_id ) , 0 )) AS propertyCount
FROM `rmp_region` r
LEFT OUTER JOIN `rmp_property` p
ON p.path LIKE CONCAT( '%/', r.region_id, '/%' )
WHERE p.active =1
AND r.parent_region_id =1
GROUP BY r.region_name, r.region_id, r.abbreviation, r.map_order
ORDER BY r.map_order ASC
I've tried different variations of this.. but i cannot get the parent records to display if the count is zero/no related records.
thanks for any help!
You need to move "p.active = 1" from the WHERE clause into the OUTER JOIN criteria.
Here's your example with the change:
SELECT r.region_name, r.region_id, r.abbreviation, r.map_order,
(IFNULL( COUNT( p.property_id ) , 0 )) AS propertyCount
FROM `rmp_region` r
LEFT OUTER JOIN `rmp_property` p
ON p.path LIKE CONCAT( '%/', r.region_id, '/%' ) and p.active =1
WHERE r.parent_region_id =1
GROUP BY r.region_name, r.region_id, r.abbreviation, r.map_order
ORDER BY r.map_order ASC
You asked about optimizing it, hoping that a index on the "path" column might help. Unfortunately you are searching for a value of path that matches LIKE CONCAT( '%/', r.region_id, '/%' )
, and no index in the world is smart enough to work with that.
精彩评论