CODEWITHSUNDEEP
mysql
VikingBlooded
VikingBlooded

Reputation: 859

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.

Upvotes: 0

Views: 112

Answers (1)

xangxiong
xangxiong

Reputation: 596

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

Upvotes: 2

Related Questions