Вопрос с заявкой на учебник...

[commander]

Вопросик такой:
БД- PostgreSQL.
Дано:
-------------------------------------------------------------------------------------
У меня есть две связанные таблицы:
CREATE TABLE train_main
(
  train_main_id serial,
  tr_num varchar(150),
  date_arr date,
  date_dep date,
  CONSTRAINT train_main_pk PRIMARY KEY (train_main_id)
);
CREATE TABLE train_plase
(
  train_plase_id serial,
  train_main_id int4,
  train_city_id int4,
  num_ost int4,
  CONSTRAINT train_plase_pk PRIMARY KEY (train_plase_id)
);

В первой содержиться номер поезда и даты начала движ. поезда и конца движ.
Во второй города следования поездов (train_city_id) и порядок их посещения
поездом (num_ost = 0 - начальный пункт, Max(num_ost) - конечный пункт).
-------------------------------------------------------------------------------------

Задача:
-------------------------------------------------------------------------------------
Вопрос в том как составить SQL запрос поиска поля  train_main_id,  зная город отправления и город прибытия, соблюдая порядок следования станций (num_ost).
-------------------------------------------------------------------------------------

[Макс]

$A - пункт отправления
$B - пункт прибытия

тогда

список поездов, выходящих из пункта А

Код: [Выделить]


   SELECT train_main_id FROM train_place
   WHERE train_place_id = $A AND num_ost =  0

получаем num_ost последней остановки для поездов выходящих из пункта А

Код: [Выделить]

  SELECT train_main_id, MAX(num_ost) as max_ost FROM train_place
   WHERE train_main_id IN (
      SELECT train_main_id FROM train_place
      WHERE train_place_id = $A AND num_ost =  0
   ) GROUP BY train_main_id


Результат

Код: [Выделить]

 
   SELECT train_main_id FROM train_place, (
      SELECT train_main_id, MAX(num_ost) as max_ost FROM train_place
      WHERE train_main_id IN (
         SELECT train_main_id FROM train_place
         WHERE train_place_id = $A AND num_ost =  0
      ) GROUP BY train_main_id
   ) as tmp
   WHERE
      train_place.train_main_id = tmp.train_main_id AND
      train_place.num_ost = tmp.max_ost AND
      train_place.train_place_id = $B


это все догадки, запросы не проверял и давно с PG не работал

[commander]

Макс
Идея мне понятна, но... фокус в том что Пункт $A не всегда являеться начальной остановкой (num_ost = 0), точно так же как и пункт $B не всегда MAX(num_ost)... Пример:
поезд № 001 СПб - МСК имеет так же остановки Тула и Балагое, И при поиске поезда идущего из Тулы в Балагое поезд 001 должен тоже найтись...

[commander]

Уфф... сам дочухал....
Идея такая:

Все поезда выходящие из пункта $A
SELECT DISTINCT train_main_id FROM train_plase WHERE train_city_id = $A

Все поезда соединяющие пункт $A и $B.
SELECT DISTINCT train_main_id FROM train_plase WHERE train_main_id IN (SELECT DISTINCT train_main_id FROM train_plase WHERE train_city_id = $A) AND train_city_id=$B

Все поезда и номера остановок пункта $B:
(SELECT DISTINCT train_main_id AS tm_id, num_ost AS n_o FROM train_plase WHERE train_city_id = 34) AS tmp

Общий результат:
SELECT DISTINCT train_main_id FROM train_plase,
(SELECT DISTINCT train_main_id AS tm_id, num_ost AS n_o FROM train_plase WHERE train_city_id = 34) AS tmp
 WHERE train_main_id IN (SELECT DISTINCT train_main_id FROM train_plase WHERE train_city_id = 34 )
AND train_city_id=35 AND num_ost>tmp.n_o AND train_main_id=tmp.tm_id

[commander]

Если кому-нить интересно в результате получилось:
---------------------------------------------------------------------------------------------------
SELECT DISTINCT m.train_main_id, m.tr_num, m.date_arr, m.date_dep,(SELECT name_eng FROM train_city WHERE train_city_id=\'$city_forward\') AS city_forward,(SELECT s.name_eng FROM train_plase p, train_station s WHERE p.train_city_id=\'$city_forward\' AND train_main_id=m.train_main_id AND p.train_station_id=s.train_station_id) AS station_forward,(SELECT time FROM train_plase WHERE train_city_id=\'$city_forward\' AND train_main_id=m.train_main_id) AS time_forward, (SELECT name_eng FROM train_city WHERE train_city_id=\'$city_back\') AS city_back,(SELECT s.name_eng FROM train_plase p, train_station s WHERE p.train_city_id=\'$city_back\' AND train_main_id=m.train_main_id AND p.train_station_id=s.train_station_id) AS station_forward,(SELECT time FROM train_plase WHERE train_city_id=\'$city_back\' AND train_main_id=m.train_main_id) AS time_back FROM train_main m, train_plase p, (SELECT DISTINCT train_main_id AS tm_id, num_ost AS n_o FROM train_plase WHERE train_city_id = $city_forward) AS tmp  WHERE p.train_main_id IN (SELECT DISTINCT train_main_id AS train_main_id_001 FROM train_plase WHERE train_city_id = $city_forward) AND p.train_city_id=$city_back AND p.num_ost>tmp.n_o AND p.train_main_id=tmp.tm_id AND m.train_main_id=p.train_main_id AND m.date_arr<=\'$date_forward\' AND m.date_dep>=\'$date_forward\'
---------------------------------------------------------------------------------------------------
И это один МЕГА запрос! )  1301 - символ...