Jay Ghosh Jay Ghosh - 28 days ago 13
MySQL Question

Joining two tables to display count of rows in second table

I have two tables,

ngo_extra_data
and
ngo_foundation_data
. Following is the structure for the tables.

ngo_extra_data

id | ngo_name | foundation_id
-----------------------------------------
1 | test ngo 1 | 1
2 | test ngo 2 | 1
3 | test ngo 3 | 2
4 | test ngo 4 | NULL


and

ngo_foundation_data

id | foundation_name
----------------------
1 | foundation 1
2 | foundation 2
3 | foundation 3


Now I want to display all the contents of ngo_foundation_data along with the number of NGOs having their
foundation_id
in ngo_extra_data as
id
in the foundation data table. So for example, 2 NGOs are registered under foundation with id
1
and 1 NGO is registered under foundation with id
2
. So the expected output would be

id | foundation_name | number_of_ngos
--------------------------------------------
1 | foundation 1 | 2
2 | foundation 2 | 1
3 | foundation 3 | 0


I tried to write the code in Magento like this

$collection = Mage::getModel('ngodata/foundations')->getCollection();
$collection->getSelect()
->joinLeft(array('ngo' => 'ngo_extra_data'),'main_table.id = ngo.foundation_id',array('foundation_id'))
->columns('COUNT(*) AS total_ngos')
->group('foundation_id');


and it appears to work apart from an exception.

When no ngo is registered under the foundation, it takes "null" as a foundation id as gives an incorrect result.

If you could give me the SQL code for the same, I could convert it to the corresponding Magento code

vkp vkp
Answer

You need a left join and a group by so that you get a 0 result for the id's that aren't in ngo_extra_data table.

select nfd.id,nfd.foundation_name,count(ned.foundation_id) cnt
from ngo_foundation_data nfd
left join ngo_extra_data ned on nfd.id=ned.foundation_id
group by nfd.id,nfd.foundation_name
Comments