im trying to see the trips that goes through both station AAA and station DDD. I have 3 tables with the following columns:

1. trip (trip_id) (route_id) (date) (time)
2. route (route_id) (starting_station) (ending_station)
3. station (station_id) (route_id) (station_name)

I tried this query and got no result back even though I had a trip that passes though both station AAA and station DDD:

SELECT 
    trip.route_id, 
    route.starting_station, 
    route.ending_station, 
    trip.date, 
    trip.time 
FROM trip 
INNER JOIN route 
    ON route.route_id = trip.route_id 
INNER JOIN station 
    ON station.route_id = route.route_id 
WHERE trip.route_id = route.route_id 
    AND route.route_id = station.route_id 
    AND station.station_name = 'AAA' 
    AND station.station_name = 'DDD';

When I ask for a single station I get back a result but with 2 stations it shows no result. Can somebody help me fix this problem and tell what exactly I did wrong please?

CodePudding user response：

You need to join with station twice since you want to set conditions on two different stations.

It should be something like this:

SELECT 
    trip.route_id, 
    route.starting_station, 
    route.ending_station, 
    trip.date, 
    trip.time 

FROM trip 
INNER JOIN route
    USING (route_id)
INNER JOIN station AS station1
    USING (route_id)
INNER JOIN station AS station2
    USING (route_id)
WHERE
    station1.station_name = 'AAA' AND 
    station2.station_name = 'DDD'

CodePudding user response：

Looks like the expression

station.station_name = 'AAA' AND station.station_name = 'DDD'

is a paradoxical predicate - how can one station have two different names!? perhaps

station.station_name = 'AAA' OR station.station_name = 'DDD'

is what you were after?