Select Column value based on two values in another column I am using the below table:

1   0051ML66220600132482    06:00:00        06:00:00        1538    100 0   1
2   0051ML66220600132482    06:00:00        06:00:00        1540    200 0   0
3   0051ML66220600132482    06:00:00        06:00:00        1541    300 0   0
4   0051ML66220600132482    06:01:00        06:01:00        1542    400 0   0
5   0051ML66220600132482    06:01:00        06:01:00        1543    500 0   0
6   0051ML66220600132482    06:02:00        06:02:00        1544    600 0   0
7   0051ML66220600132482    06:03:00        06:03:00        1546    700 0   0


Table structure is like below:

------------------------------------------------------------------
--  TABLE stop_times
------------------------------------------------------------------ 
CREATE TABLE stop_times 
( 
    id int(12),   
    trip_id varchar(100),
    arrival_time varchar(8),
    arrival_time_seconds int(100),
    departure_time varchar(8),
    departure_time_seconds int(100),
    stop_id varchar(100),
    stop_sequence varchar(100),
    pickup_type varchar(2),
    drop_off_type varchar(2)
);


I am attempting to get DISTINCT trip_id if it matches both the destination and arrival stop_id

I have tried the below SQL with no luck:

select DISTINCT trip_id from stop_times where stop_id=1538 AND stop_id =1540;


Where it should produce: 0051ML66220600132482

I have also tried an INNER JOIN like below:

SELECT 
    t.trip_id,
    start_s.stop_name as departure_stop,
    end_s.stop_name as arrival_stop
FROM trips t 
INNER JOIN stop_times start_st ON t.trip_id = start_st.trip_id
INNER JOIN stops start_s ON start_st.stop_id = start_s.stop_id
INNER JOIN stop_times end_st ON t.trip_id = end_st.trip_id
INNER JOIN stops end_s ON end_st.stop_id = end_s.stop_id
WHERE 
   start_s.stop_id = 1538 
  AND end_s.stop_id = 1540;


But it is sooo slow, and takes about 8-15 seconds for this simple query. 

Explain Added:



What is the fastest/best way to write this query?
If I understand, you want to get all trip_id values that appear in the table, against one stop_id on one or more rows, and against another stop_id on other rows. This should do it:

select trip_id 
from stop_times 
where stop_id = 1538 
or stop_id = 1540
group by trip_id
having count(distinct stop_id) &gt; 1;


Example: http://sqlfiddle.com/#!9/e78aa/1

Notice that trip_id 0051ML66220600132483 matches on one stop but not the other, so is not returned.