MYSQL И запрос для удовлетворения в одном столбце

Цель проекта:

Мы разрабатываем время шины Api, где пользователь будет искать шины.

Ниже приведены мои table structure

У меня есть следующий tables

автобусы

id | bus_name

Описание таблицы: Сохранить все имена автобусов

маршруты

id | route_name

Описание таблицы: Сохранить все названия городов

останавливается

id | stop_name

Описание таблицы: Все имена остановки

stop_orders

id | route_id | stop_id | stop_order

Описание таблицы: здесь я назначу стопы для справки столбца city и stop_order, чтобы определить, какие остановки рядом друг с другом

bus_timing

id | stop_order_id | bus_id | bus_timing | trip | trip_direction

Описание таблицы: Здесь я буду назначать шины для остановки маршрута вместе с временем, отключением и направлением

Ожидаемый результат:

Когда пользователь ищет между источником и получателем со временем, Api должен возвращать весь список автобусов со временем
Если прямые шины не там, то взаимосвязанные шины должны показывать

Например, если пользователь ищет между stop_8 до stop_18 с 01:00:00 до 12:00:00, то все списки автобусов со временем должны показывать. Если прямые шины не там, чтобы перемещаться между двумя остановками, тогда список взаимосвязанных списков маршрутов должен показывать

Результат, который я получил,

PHP сопоставляет ассоциативный массив на основе условия

Настоящая проблема с результатами возврата

Он будет возвращать все шины, даже если шина только перемещается в stop_8, но не stop_18. Но мой результат должен возвращать только те шины, которые будут перемещаться между двумя остановками, я имею в виду, что он должен падать между обеими остановками.
Даже я понятия не имею, как найти список взаимосвязанных автобусов
Если временной диапазон длинный, тогда вероятность того, что одна и та же шина будет перемещаться (отключение и направление) несколько раз

Обновление Все еще ищут ответ. Правильно, теперь у ответа есть некоторые моменты, так предлагаемые награды

Ответ 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;

Ответ 2

Кроме того, если автобусы не перемещаются напрямую между двумя городами, мне нужно показать межподключенные шины.

Это серьезная проблема в классе проблем, называемых проблемы маршрутизации.. Для этого вам нужен лучший инструмент: рассмотрите возможность миграции или интеграции PostgreSQL и рассмотрите PgRouting, в частности, вам, скорее всего, понадобится Shorty Path Dijkstra. PgRouting работает поверх расширения PostGIS.

Или подумайте над тем, чтобы интегрироваться с Esri.

В качестве альтернативы вы можете обходиться с этим, но я бы не советовал это.

OQgraph (обновление)

Из symcbean в комментариях вы можете использовать движок базы данных OQgraph для этого. Ниже приведен пример кратчайший путь.