MYSQL AND query to satisfy on same column

Question

Project Aim :

We are developing bus timing Api where user will search for buses.

Following are my table structure

I have following tables

buses

id | bus_name 

Description of table: Store all buses Names

routes

id | route_name 

Description of table: Store All city names

stops

id | stop_name 

Description of table: All stop names

stop_orders

id | route_id | stop_id | stop_order 

Description of table: here i will assign stops for city and stop_order column help to identify which stop next to each other

bus_timing

id | stop_order_id | bus_id | bus_timing | trip | trip_direction 

Description of table: Here i will assign buses for route stops along with time and trip and direction

Output Expecting:

1. When user search between source to destination with time then Api must return all buses list with time

2. if direct buses not there then interconnected buses should show

For example if user search between stop_8 to stop_18 with 01:00:00 to 12:00:00 then all buses list with time should show.if direct buses not there to travel between two stops then interconnected link buses list should show

Output what i got is

PHP compare associative array based on condition

Present return result issue is

1. It will return all buses even though if bus is only travel to stop_8 but not stop_18.But my result must return only those buses which will travel between two stops i mean it must fall between both stops .

2. Even i have no idea how to find interconnected buses list

3. When time range is long then there is chance of same bus will travel(trip and direction) multiple times

Updates Still looking for answer .Right now given answer has some points so offered bounty

Answer 1

Because stop_id cannot be two different values in the same row.

Aggregation is one way to do what you want:

SELECT b.bus_name FROM buses b JOIN      route_connect rc      ON rc.busid = b.id JOIN      stops s      ON s.id = rc.stop_id GROUP BY b.bus_name HAVING SUM( s.stop_name = 'Sydney' ) > 0 AND        SUM( s.stop_name = 'Melbourne' ) > 0; 

This returns buses that have stops with the name of both cities.

Given that buses can have lots of stops, it might be more efficient to do:

SELECT b.bus_name FROM buses b JOIN      route_connect rc      ON rc.busid = b.id JOIN      stops s      ON s.id = rc.stop_id WHERE s.stop_name in ('Sydney', 'Melbourne') GROUP BY b.bus_name HAVING COUNT(DISTINCT s.stop_name) = 2; 

Answer 2

Also if buses are not directly travel between two city then i need to show inter connected buses.

That's a massive problem in a class of problems called routing problems.. For it, you need a better tool: consider migrating or integrating PostgreSQL, and examining PgRouting specifically you'll likely want Dijkstra's Shortest Path. PgRouting runs atop the PostGIS extension.

Or, consider working on integrating with Esri.

Alternatively you can mess around with this, but I wouldn't advise it.

OQgraph (update)

From symcbean in the comments, you could use the "OQgraph database engine" to do this too. There is an example of shortest path here.