Compare different rows in SQL

234
February 02, 2017, at 02:07 AM

I have 3 tables:

Section (id, origin, destination, departure_time, arrival_time)
1 - city1 - city2 - 2017-01-01 10:00:00 - 2017-01-01 12:00:00
2 - city2 - city3 - 2017-01-01 12:00:00 - 2017-01-01 13:00:00
3 - city3 - city4 - 2017-01-01 13:00:00 - 2017-01-01 15:00:00
4 - city4 - city5 - 2017-01-01 15:00:00 - 2017-01-01 16:30:00 
Trip_Section(trip_id, section_id)
1 - 1
1 - 2
1 - 3
1 - 4
Trip(id, from, to, departure_time)
1 - city1 - city5 - 2017-01-01 10:00:00

Table sections contains all stops of a trip, and i need search all trips that contains an origin and destination requested. In this part i'm trying to recover the trips with the parameters in different sections. For example: i want to travel from city2 to city4 so my query is:

select ts.trip_id, count(ts.trip_id) >= 2 from Section as s 
inner join Trip_Section ts on ts.sections_id = s.id
where (s.origin = 'city2' OR s.destination = 'city4')
GROUP BY ts.trip_id having count(ts.trip_id) >= 2;

and the result is correct, but if i search from city4 to city2, the result is not empty, is the same than the first case.

How can i compare the different rows to return only the rows when the departure_time of origin will be smaller than departure_time of destination (or another posibility is check the id of destination is greater than id of origin). Can someone help me?

Answer 1
  • Find the section with origin = 'city2'
  • Find the Trip for that section
  • Find the section from the same trip with destionation='city4'
  • Find all sections from that trip between the two sections above

All in one query:

select tso.trip_id, s.*
from Section so
join Trip_Section tso on tso.section_id = so.id
join Trip_Section tsd on tsd.trip_id = tsd.trip_id
join Section sd       on sd.id = tsd.section_id
join Trip_Section ts  on ts.trip_id = tso.trip_id
join Section s        on s.id = ts.section_id
where so.origin      = 'city1'
  and sd.destination = 'city4'
  and so.departure_time <= sd.departure_time
  and s.departure_time  >= so.departure_time
  and s.departure_time  <= sd.departure_time

This will give you an overview of all stops between two cities per trip.

If you don't need all stops you can also try this one:

select tso.trip_id, so.origin, so.departure_time, sd.destination, sd.arrival_time
from Section so
join Trip_Section tso on tso.section_id = so.id
join Trip_Section tsd on tsd.trip_id = tsd.trip_id
join Section sd       on sd.id = tsd.section_id
where so.origin      = 'city2'
  and sd.destination = 'city4'
  and so.departure_time <= sd.departure_time

http://rextester.com/GGPHT16173

READ ALSO
What to do if merge, join, and left_join fail?

What to do if merge, join, and left_join fail?

I'm trying to do a "vlookup" in a dataframe without freezing RStudio

263
Left join query

Left join query

I did the following question some days ago

178
AutoMySQLBackup Missing Rotation Setting in Debian / Ubuntu Version?

AutoMySQLBackup Missing Rotation Setting in Debian / Ubuntu Version?

Why are there no rotation settings in the Debian / Ubuntu Version of AutoMySqlBackup? (config file is at /etc/default/automysqlbackup)

367