1
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.

0

1 Answer 1

2

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
1
  • @Ergest Basha you query runs but it do not gives counts. But now, I got it. It should be this. left join (select count(id) total_vehicles,school_id from vehicles) as vehicles on vehicles.school_id= schools.id. this one works. thanks for the help.
    – Priyal
    Mar 10, 2023 at 12:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.