Context
I am writing an application that does a variation on the Vehicle Routing Problem. The application has routes, stops and driving directions for routes. I need to write a query for a view that combines all the relevant attributes for a route. Hence I need to join the routes table to multiple many to many relationships in a single query.
Query Details
There is the routes table, the route_stop_join table and the route directions table. The relationship between routes and stops is really many to many but we only need a list of stop ids, so suffices to consider the one to many relationship with the join table. The following query counts the sums n time where n is the number of stops:
select r.id,
array_agg(j.stop_id) as stops,
sum(rd.time_elapsed) as total_time,
sum(rd.drive_distance) as total_distance
from routes_directions rd
right join routes r
on rd.route_id = r.id
left join routes_stops_join j
on r.id = j.route_id
group by r.id;
I can do this using a subselect like this:
select rj.id,
rj.stops,
sum(rd.time_elapsed) as total_time,
sum(rd.drive_distance) as total_distance
from routes_directions rd
right join (select r.id,
array_agg(j.stop_id) as stops
from routes r
left join routes_stops_join j
on r.id = j.route_id
group by r.id) rj
on rj.id = rd.route_id
group by rj.id, rj.stops;
but I would like to see if there is a way to do this in a single query without subselects.
As far as this query returns 99% of the information you need:
select rd.id,
sum(rd.time_elapsed) as total_time,
sum(rd.drive_distance) as total_distance
from routes_directions rd
group by rd.id;
I'd suggest to use a subquery or a CTE, but using LEFT JOIN instead of RIGHT JOIN.
create table routes(id int); insert into routes values (1),(2); create table routes_stops(route_id int, stop_id int); insert into routes_stops values (1,1),(1,2),(2,1),(2,3),(2,4); create table routes_directions(route_id int, dir_id int, time_elapsed int, drive_distance int); insert into routes_directions values (1,1,100,40),(1,2,60,60),(2,1,15,14),(2,3,20,30);
select rj.id, rj.stops, sum(rd.time_elapsed) as total_time, sum(rd.drive_distance) as total_distance from routes_directions rd left join (select r.id, array_agg(j.stop_id) as stops from routes r left join routes_stops j on r.id = j.route_id group by r.id) rj on rj.id = rd.route_id group by rj.id, rj.stops;id | stops | total_time | total_distance -: | :------ | ---------: | -------------: 2 | {1,3,4} | 35 | 44 1 | {1,2} | 160 | 100
with stp as ( select r.id, array_agg(j.stop_id) as stops from routes r left join routes_stops j on r.id = j.route_id group by r.id ) select rd.route_id, stp.stops, sum(rd.time_elapsed) as total_time, sum(rd.drive_distance) as total_distance from routes_directions rd left join stp on stp.id = rd.route_id group by rd.route_id, stp.stops;route_id | stops | total_time | total_distance -------: | :------ | ---------: | -------------: 1 | {1,2} | 160 | 100 2 | {1,3,4} | 35 | 44
dbfiddle here