I'm not entirely sure, and I cannot test it right now, but I think on the last (left) join you are introducing elements which seem to belong to a
WHERE
clause in a
JOIN
statement.
Maybe try:
select sm.sugar_mill_name, mbz.mill, fhi.hczname as zone, sd.name as station,
round(coalesce(wd.dvalue, 0), 1) as Station_Reading
from sugar_mills sm join
mill_by_zone mbz
on sm.sugar_mill_name = mbz.mill join
fca_hcz_info fhi
on fhi.hcz = mbz.zone join
zone_by_station zbs
on zbs.zone = fhi.hcz join
station_details sd
on sd.station_num = zbs.station left join
weekly_data wd
on wd.station_num = sd.station_num
where wd.record_year = 2019 and
wd.record_week = 19 and
wd.dcode = 1;
Moreover,
wd.station_num
is already joined to
sd.station_num
, which itself is joined to
zbs.station
; there is no need to account for
wd.station_num = zbs.station
.
Hope this helps.