here are some data from the tables

slm

slm_activities

period

activities

i need to get this kind of output for mysql it will display all kinds of activities and for all periods then sum their count value, activitycount will display 0 if it doesn't encounter other activities for that period

this is my current mysql query

SELECT SUM(count) AS activitycount, activities.activity, periods.period
FROM slm_activities 
LEFT JOIN activities ON activities.id = slm_activities.activity 
LEFT JOIN slm ON slm_activities.slmid = slm.id 
LEFT JOIN periods ON periods.period = slm.period
GROUP BY activities.activity, periods.period

CodePudding user response：

Since you need to show all periods for each activities, you need to use CROSS JOIN for this. Also the table slm should store the id of periods table instead of period field.

SELECT SUM(IFNULL(count, 0)) AS activitycount, activities.activity, periods.period
FROM activities
CROSS JOIN periods
LEFT JOIN slm ON periods.period = slm.period
LEFT JOIN slm_activities ON activities.id = slm_activities.activity
 AND slm.id = slm_activities.slmid
GROUP BY activities.activity, periods.period
ORDER BY activities.activity, periods.period;

Result

See this fiddle.