After hours trying to figure out what's wrong with my query for displaying list of cities with how many Lease/Sale were there. The problem was getting the cities even not yet added on Lease or Sale type, that means I need to list cities that has no match yet or 0 count.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | <? global $mainframe; $ebconfig = &$mainframe->getParams(); $prop_Type = $ebconfig->get('prop_type', "Lease"); $db_city =& JFactory::getDBO(); $query_city = "SELECT t1.city, t1.id, COUNT( t2.propCity ) AS city_count FROM #__listing_city AS t1 LEFT OUTER JOIN #__listing_listings AS t2 ON t2.propCity = t1.city WHERE t2.propType = '$prop_Type' AND t2.published = 1 GROUP BY t1.city ORDER BY t1.city ASC"; ?> |
The code above does not display the cities that has no match on another table. The where clause was giving the problem...
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | <? global $mainframe; $ebconfig = &$mainframe->getParams(); $prop_Type = $ebconfig->get('prop_type', "Lease"); $db_city =& JFactory::getDBO(); $query_city = "SELECT t1.city, t1.id, COUNT( t2.propCity ) AS city_count FROM #__listing_city AS t1 LEFT OUTER JOIN #__listing_listings AS t2 ON t2.propCity = t1.city AND t2.published = 1 AND t2.propType = '$prop_Type' GROUP BY t1.city ORDER BY t1.city ASC"; ?> |
Instead putting the condition on "ON Clause" after the matching table condition, fixed the problem.