CODEWITHSUNDEEP
mysqlrow
Mithun Sudheendran
Mithun Sudheendran

Reputation: 27

return a mysql table column according to column conditions

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

Answers (2)

James Green
James Green

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

jvilhena
jvilhena

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

Related Questions