This query gives error. right syntax to use near 'on vehicles.school_id = schools.id left join drivers on drivers.school_id = ...' at line 8

Question

select drc_users.name,
vehicles.total_vehicles,
COUNT(distinct drivers.id) as total_drivers
from drc_users
LEFT join brc_users on drc_users.id = brc_users.district_id
left join crc_users on crc_users.block_id = brc_users.id
left join schools on schools.cluster_id = crc_users.id
left join (select count(id) total_vehicles from vehicles) on vehicles.school_id = schools.id
left join drivers on drivers.school_id = schools.id
where drc_users.district_id = 2401

I am using mariadb 10.5.12

Need help with this query issue.

Answer 1

The error is in the following line:

left join (select count(id) total_vehicles from vehicles) 
    on vehicles.school_id = schools.id

Each query in brackets must be given an alias, which can then be used to refer to it in the rest of the outer query.

Change it to:

left join (select count(id) total_vehicles from vehicles) as vehicles 
    on vehicles.total_vehicles = schools.id

---

but it still not working. #1054 - Unknown column 'vehicles.school_id' in 'on clause'.

That should be vehicles.total_vehicles because inside the subquery you have:

 count(id) total_vehicles