How can I do this complex SQL query using Django ORM? (sub-query with a join)

It'd probably be a bit easier to figure out the appropriate way to do this if you had what you were using for the relevant Models.

I'm assuming something like the following, based on the specification you mentioned working from:

class Route(models.Model):
    #bunch of stuff
    pass
class Stop(models.Model):
    #bunch of stuff
    stop_times = models.ManyToManyField(through=StopTime)
class StopTime(models.Model):
    trip = models.ForeignKey(Trip)
    stop = models.ForeignKey(Stop)
    # bunch of additional meta about this M2M table
    pass
class Trip(models.Model):
    route = models.ForeignKey(Route)
    # bunch of stuff

If that's the case... you should be able to do something like

Route.objects.filter(trip__stop__id=my_stop_id)

to get all Route objects that go through a given Stop with a primary key id equal to my_stop_id, which I'm assuming is an integer as per your post.

I apologize if the syntax is a bit off, as I haven't needed to do many-to-many relationships using an explicit extra table. Some adjustment may also be needed if you have to (or choose to) use the related_name parameter for any the foreign keys or the many-to-many-field.

Correct me if I'm wrong, but I don't think you can do that with Django ORM in a normal way.

There is no subquery support and with a normal join it would depend on your database if a distinct could help you. If you are using Postgres than you could do it with this patch: http://code.djangoproject.com/ticket/6422

The query would be something like this:

Route.objects.filter(stop_time__trips__stop_id=...).distinct('stop_time__route_id')