COMPLEX SQL join 3 table with count and filter using selected date and area

Question

i want to make list guide with count of their tripguide based on area per current month so it will easier to assign work to guide with less count first, so theres equality on number of jobs per area in selected date/month. it will show all guide in the area even with zero tripguide done.

#guide :id, name, area

#trips: id, tripdate, area,

#tripguide: id, trip_id, guide_id, status,

desired result

name        work
guideA      2
guideB      1
guideC      0 

my query is

SELECT g.id, g.name,g.area,COUNT(tg.guide_id) AS work 
FROM tripguide AS tg 
LEFT JOIN trips AS t ON tg.trip_id = t.id
LEFT JOIN guide AS g ON tg.guide_id = g.id 
WHERE t.area = '1' AND tg.status = 1 AND t.tripdate >= DATE_SUB(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY) AND t.tripdate <= LAST_DAY(CURRENT_DATE)
GROUP BY g.id; 

the problem is guide with no record on tripguide not show.

here the SQL fiddle

Thank you for the suggestion & answer

Answer 1

You are selecting tripguide rows, outer joining the other tables. This means you would even show tripguide rows that have no associated guide or trip. Such rows don't even exist. You want to select guides, even when they have no tripguides/trips.

Here is the corrected query:

SELECT g.id, g.name, g.area, COUNT(t.id) AS work 
FROM guide AS g 
LEFT JOIN tripguide AS tg ON tg.guide_id = g.id 
                         AND tg.status = 1 
LEFT JOIN trips AS t ON t.id = tg.trip_id
                    AND t.area = 1 
                    AND t.tripdate >= DATE_SUB(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY)
                    AND t.tripdate <= LAST_DAY(CURRENT_DATE)
GROUP BY g.id
ORDER BY work DESC, g.id;

This shows all guides along with their status-1 trips in area 1. Even if the guide's area is not area 1. If you want to limit this to area-1 guides, you'll have to add WHERE g.area = 1 of course.