Reputation: 27
i have a table containing route id's for buses with bus stops.. like below..
route_id stop_name
1234 stop1
1234 stop2
1235 stop7
1235 stop8
i want a route_id that contains both stop1 and stop2 (for stop1 and stop2 i would like to get 1234)
i wrote this query..
SELECT DISTINCT(route_id)
FROM bus_stops
WHERE stops='stop1' AND stops='stop2'
but it returned an empty set.
Upvotes: 1
Views: 58
Reputation: 1763
You could try this, but I don't promise it's the best or most elegant solution:
SELECT DISTINCT route_id FROM myTable AS myAlias
WHERE EXISTS ( SELECT * FROM myTable WHERE route_id = myAlias.route_id AND stop_name = 'stop1' )
AND EXISTS ( SELECT * FROM myTable WHERE route_id = myAlias.route_id AND stop_name = 'stop2' )
Actually, I don't even promise it's tested -- but using subqueries similar to those ought to get you there.
Upvotes: 2
Reputation: 1131
try this:
select distint route_id from bus_stops s1
where exists(select 1 from bus_stops s2 where s1.route_id = s2.route_id and s2.stop_name = 'stop1')
and exists(select 1 from bus_stops s2 where s1.route_id = s2.route_id and s2.stop_name = 'stop2')
it will give you the routes that have both the stops.
Upvotes: 3