Amc Amc - 1 year ago 54
MySQL Question

MySQL Left Join Count Activities and return null

trying to get this mysql query to work.

Explanation: There are two tables, one with all projects with org id columns, the second has all activities with column projectid that relates to the project table. I would like to count all activities within the activities table that has the userid and is active with 1.
But here comes the tricky part, I cannot get working. It does not collect the projects that are missing in activities table and present these with count 0. I would like to show all project that correspond with a unique projectid within bo

SELECT
p1.`id` AS projectid
,count(a1.`id`) AS activitiescount
,a1.`userid` AS userid
,p1.`orgid` AS orgid

FROM `projects` AS p1
LEFT JOIN `activities` AS a1 ON p1.`id`=a1.`projectid`

WHERE p1.`orgid`=3 AND a1.`userid` = 26 AND a1.`active`=1
OR a1.`id` IS null GROUP BY p1.`id` ORDER BY p1.`name` DESC


Gives:

enter image description here

As you can see, the is not correct with orgid 6 and 2 for example.
Please help. Thank you.

Answer Source

Untested:

Moved limits on Activities to the join.

Also the OR brought back more than you wanted, by moving the where clause around that problem should be resolved as well.

Standard Approach:

SELECT p1.`id` AS projectid 
     , count(a1.`id`) AS activitiescount 
     , a1.`userid` AS userid 
     , p1.`orgid` AS orgid  
FROM `projects` AS p1 
LEFT JOIN `activities` AS a1 
  ON p1.`id`=a1.`projectid` 
 AND a1.`userid` = 26 
 AND a1.`active`=1 
WHERE p1.`orgid`=3 
GROUP BY p1.`id` 
ORDER BY p1.`name` DESC

your approach w/ ()'s correctly placed.

SELECT 
    p1.`id` AS projectid 
    ,count(a1.`id`) AS activitiescount 
    ,a1.`userid` AS userid 
    ,p1.`orgid` AS orgid  

FROM `projects` AS p1 
LEFT JOIN `activities` AS a1 ON p1.`id`=a1.`projectid` 

WHERE p1.`orgid`=3 
  AND ((a1.`userid` = 26 AND a1.`active`=1)  OR a1.`id` IS null) 
GROUP BY p1.`id` 
ORDER BY p1.`name` DESC
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download