I need to find how many resources are available in depot when transit arrives. I need to have transit name and resource count on result. I'm using MySQL. I wrote this but it gives me error when adding columns to upper select.
select j.name, m.rescount
(select count(*) as rescount
from depot_resource as m
where j.scheduled_time between m.actual_start and m.actual_end)
from transit as j
You can't reference depot_resource in the outer query, it is out of scope. You need to join the tables, and group by name to get the aggregate. Since you didn't provide DDL and sample data, we can only guess, but maybe you need something like this?
SELECT j.name, COUNT(*) AS rescount
FROM transit as j
INNER JOIN
depot_resource as m
on j.scheduled_time between m.actual_start and m.actual_end
GROUP BY j.name
Something seems odd here as there is no correlation to j.name in depot_resource, but without the table structure and the expected result we can't really tell.
HTH
