MYSQL And query to satisfy in one column

Question

Objective of the project:

We design tire times Apiwhere the user will look for tires.

Below are my table structure

I have the following tables

the buses

id | bus_name

Table description: Save all bus names

routes

id | route_name

Description of the table: Save all city names

stops

id | stop_name

Table Description: All Stop Names

stop_orders

id | route_id | stop_id | stop_order

Description of the table: here I will assign stops for the help of the city and stop_order columns to determine which stops are next to each other

bus_timing

id | stop_order_id | bus_id | bus_timing | trip | trip_direction

Description of the table: Here I will assign buses to stop the route along with time, shutdown and direction

Expected Result:

• , Api

• ,

, stop_8 stop_18 01:00:00 12:00:00, . , ,

, ,

PHP

• , stop_8, stop_18. , , , .

• ,

• , , ( )

. , ,

Answer 1

stop_id .

- , :

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;

, .

, , :

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

, , .

, .. : PostgreSQL PgRouting, , , , Shorty Path Dijkstra. PgRouting PostGIS.

, Esri.

, .

OQgraph ()

symcbean OQgraph . .