Don't know how to join multipe foreign keys in a single table

Question

I created these 4 table:

CITY

city_id	city
1	AAA
2	BBB
3	CCC
4	DDD

TRIPS

trip_id	route_id	date	time
1	1	...	...

ROUTES

route_id	city_id(from)	city_id(to)
1	1(AAA)	3(CCC)
2	1(AAA)	4(DDD)

STOPS

stop_id	route_id	city_on_the_way
1	1(AAA->CCC)	1(AAA)
2	1	2(BBB)
3	1	3(CCC)
4	2(AAA->DDD)	1(AAA)
5	2	2(BBB)
6	2	3(CCC)
7	2	4(DDD)

Im trying to get the trips that pass through city AAA and DDD at the time. This is what I started with but I just dont know how to join the rest with each other. I tried many things and I keep getting errors. Can someone please explain to me what I need to make this work?

SELECT 
 trips.trip_id, 
 city.city AS startpoint, 
 city.city AS endpoint, 
 trips.date, 
 trips.time 
FROM 
 trip
INNER JOIN
 route ON trips.route_id = routes.route_id  
INNER JOIN
 city ON routes.city_id(from) = city.city_id;

Answer 1

This should do it (updated!):

WITH stps AS ( SELECT s.route_id rid, city FROM stops s INNER JOIN cities ON city_id=city_on_the_way )
SELECT  trip_id,  'AAA' city1, 'DDD' city2, dt,  ti 
FROM  trips WHERE EXISTS ( SELECT 1 FROM stps WHERE city='AAA' AND rid=route_id)
              AND EXISTS ( SELECT 1 FROM stps WHERE city='DDD' AND rid=route_id)

I still believe that the table ROUTES is not really helpful in this context. The above code can be seen in action here: dbfiddle.uk.

And I disagree with OP that there must be two results: Only trip no. 1 exists in the trips table. In my demo I actually added a second record into the trips table. Now both routes (1 and 2) are linked to trip no. 1, but only route 2 includes both destinations.

Answer 2

In this case, your starting point should be the stops table:

SELECT
  t.trip_id
FROM stops s
JOIN trips  t ON r.route_id = s.route_id
WHERE s.city_on_the_way IN (
  SELECT CONCAT(city_id, "(", city, ")") as city_on_the_way
  FROM city
  WHERE city IN ("AAA", "DDD")
)
GROUP BY s.route_id, t.trip_id
HAVING COUNT(1) = X 

// Replace X with the number of cities you desire: in this example X = 2  
// So, if you want city AAA, BBB, CCC, X will be 3