Q:

MySQL query count of grandchildren with where clause

Ok, I have looked and I cant seem to find an answer to this and I really need some help.
I have 3 tables: 
m(id, name), p(id, m_id, name), t(id, p_id, date, type, status)

They are joined as:
m.id = p.m_id
p.id = t.p_id

I need to get a count of t.type where t.type = approved
grouped by m.id and by p.id
This is what I have so far, but I get errors with "subquery return multiple rows" or I just get a total count that is applied to all records.
SELECT
          m.`id`,
          m.`company`,
          (SELECT COUNT(t.`type`) 
          FROM t 
          INNER JOIN p on p.`id`=t.`p_id` 
          INNER JOIN m on p.`m_id`= m.`id`  
          WHERE t.`type`='approved' GROUP BY m.`id`) AS `app`
        FROM m
          INNER JOIN p
            ON m.`id` = p.`m_id`
          INNER JOIN t
            ON p.`id` = t.`p_id`
        WHERE t.`date` BETWEEN '2015-11-01 00:00:00' AND NOW() 
        GROUP BY m.`id`
        ORDER BY m.`company

I am completely lost here.

A:

You can try and change your query to use COUNT instead which will help you avoid subquery.  Something like the query below should get you the results you want.
SELECT
   m.`id`,
   m.`company`,
   COUNT(IF(t.`type` = 'approved', t.`type`, NULL)) AS `app`,
   COUNT(IF(t.`type` = 'declined', t.`type`, NULL)) AS `dec`
FROM m
   INNER JOIN p
      ON m.`id` = p.`m_id`
   INNER JOIN t
      ON p.`id` = t.`p_id`
WHERE t.`date` BETWEEN '2015-11-01 00:00:00' AND NOW() 
   AND t.`type` IN ('approved', 'declined')
GROUP BY m.`id`
ORDER BY m.`company

