2.5. Запрос, использующий связь таблицы с самой собой (рекурсивное соединение)

Допустим, нужно получить выборку сотрудников, заказов и клиентов, для которых дата размещения заказов совпадает с датой исполнения за 1998 год. Для этого откройте новый запрос в режиме конструктора и добавьте в него таблицы Заказы и Сотрудники, причем Заказы — два раза. В результате система автоматически присвоит для копии таблицы Заказы имя Заказы_1. К уже имеющейся постоянной связи между таблицами Заказы и Сотрудники нужно добавить связь между таблицами Заказы и Заказы_1 по полям ДатаРазмещения и ДатаИсполнения. Такая связь называется рекурсивным соединением. Для того чтобы запрос работал правильно, нужно также добавить связь по полям КодЗаказа таблиц Заказы и Заказы_1.

В первое поле бланка запроса введите определение: Сотрудник: [Фамилия] & " " & [Имя] и добавьте поля Должность из таблицы Сотрудники и КодЗаказа, КодКлиента и Дата Размещения из таблицы Заказы. Для полей Сотрудник и Дата Размещения установите порядок сортировки по возрастанию и для поля ДатаРазмещения укажите условие: Between #01.01.98# And #31.12.98#. Полученный запрос изображен на рис. 2.5.

Рис 2.5. Запрос, использующий рекурсивное соединение таблиц.

Пояснения

а). При построении этого запроса есть один тонкий момент, незнание которого может привести к получению ошибочной итоговой выборки. Если между таблицами Заказы и Заказы_1 будет только одна связь по полям ДатаРазмещения и ДатаИсполнения,, то будут выбираться те записи, в которых оба поля совпадают, в том числе и для совершенно разных заказов.

Для того чтобы ограничиться заказами, для каждого из которых поля ДатаРазмещения и Дата Исполнения имеют одинаковые значения, нужно связать также поля КодЗаказа таблиц Заказы и Заказы_1.

б). Изначально таблица Заказы учебной базы данных Борей не имеет ни одной записи, содержащей одинаковые значения в полях ДатаРазмещения и ДатаИсполнения. Для того чтобы продемонстрировать способ использования рекурсивной связи, пришлось вручную подкорректировать даты одной из записей.

Если добавить в бланк запроса сначала дважды таблицу Заказы и затем — Сотрудники, то конструктор также автоматически свяжет таблицу Сотрудники с копией таблицы Заказы — Заказы_1 по полю КодСотрудника. Эта связь лишняя и ее нужно убрать. Если добавлять в макет запроса таблицы в обратной последовательности, то лишняя связь создана не будет.

2.6. Запрос, использующий две ссылки на одну таблицу

Иногда возникает проблема, сводящаяся к тому, что итоговый запрос должен содержать несколько полей, ссылающихся на одну и ту же таблицу-справочник. Типичным примером указанной ситуации является документ (накладная) на перемещение товарно-материальных ценностей (ТМЦ) с одного склада предприятия на другой. Принципы построения реляционных баз данных требуют, чтобы в полях ИсходныйСклад и СкладНазначения хранились ссылки на соответствующее поле таблицы-справочника Список складов. Создавая запрос, отображающий позиции всех таких накладных, мы можем одну из ссылок связать с таблицей-справочником Список складов, чтобы в итоговой выборке было представлено не абстрактное число, а, например, полное наименование склада, фамилия материально-ответственного лица и другая соответствующая информация. Однако, если связать с таблицей Список складов и вторую ссылку, то в итоговую выборку попадут только те записи, в которых ИсходныйСклад и Склад Назначения совпадают.

Прежде чем приступить к описанию решения такой проблемы, следует проделать несколько предварительных действий. Перейдите на вкладку Таблицы окна базы данных и создайте две новых таблицы: Список отделов и Список сотрудников по отделам. Первая таблица должна содержать следующие поля: Номер числового типа, размер — длинное целое; Отдел — текстового типа; Начальник — числового типа, размер — длинное целое. Далее перейдите в режим просмотра и введите в таблицу информацию в соответствии с рис. 2.6. Эта таблица будет служить для распределения всех сотрудников по различным отделам.

Вторая таблица, Список сотрудников по отделам, должна содержать два поля — Сотрудник и Отдел. Оба поля должны быть числового типа, размер — длинное целое. Использование этой таблицы является ярким примером того, как можно расширить уже готовую структуру базы данных, при этом, не нарушая ее. С другой стороны, эта таблица является примером применения связей многие-ко-многим, поскольку будет служить для связывания многих записей из таблицы Сотрудники со многими записями из таблицы Список отделов. После окончания конструирования перейдите в режим просмотра и заполните таблицу информацией в соответствии с рис. 2.6.

Рис 2.6. Вспомогательные таблицы Список отделов и Список сотрудников

Далее перейдите на вкладку Запросы окна базы данных и создайте новый запрос, открыв его в режиме конструктора. Добавьте в его макет таблицы Список отделов, Список сотрудников по отделам и таблицу Сотрудники — дважды. Между таблицами создайте следующие связи: между полем Начальник таблицы Список отделов и полем КодСотрудника таблицы Сотрудники; между полем Номер таблицы Список отделов и полем Отдел таблицы Список сотрудников по отделам; между полем Сотрудник таблицы Список сотрудников по отделам и полем КодСотрудника таблицы Сотрудники!.

После этого перетащите в бланк запроса поле Отдел из таблицы Список отделов, в определение второго поля введите текст: Сотрудник: Сотрудники_1.Фамилия & " " & Сотрудники_1.Имя, а в определение третьего — Начальник:Сотрудники.Фамилия & " " & Сотрудники.Имя. В заключение установите для первых двух полей сортировку По возрастанию. Получившийся запрос представлен на рис. 2.7.

Рис 2.7. Запрос, использующий две ссылки на одну таблицу.

Пояснения

а). Если из бланка запроса (см. рис. 2.7) удалить копию таблицы Сотрудники и связать поля Начальник таблицы Список отделов и Сотрудник таблицы Список сотрудников по отделам с полем КодСотрудника таблицы Сотрудники, то в итоговой выборке будет только список руководителей отделов. Действительно, установленные таким образом связи подразумевают, что отбираться будут только те записи из трех таблиц, в которых значения всех трех полей совпадают. Получается замкнутый круг. Чтобы разорвать этот круг и применяются копии таблиц. В описываемом примере существует одно лишнее поле, для которого в макет запроса была добавлена одна копия таблицы. Если в запросе будет два лишних поля — то нужно будет использовать две копии таблицы, и т.д.

б). Вспомогательная таблица Список сотрудников по отделам используется для создания связи типа многие-ко-многим между таблицами Список отделов Сотрудники. В таких таблицах обычно содержатся исключительно ключевые поля связываемых таблиц.

в) В приведенном примере для связывания всех таблиц использовались временные связи.

г) Обратите внимание на то, как в строке оператора SQL в предложении FROM вводится копия таблицы Сотрудники:

Сотрудники AS Сотрудники_1

Эта конструкция указывает на то, что далее в операторе SQL имя Сотрудники_1 будет употребляться как синоним имени таблицы Сотрудники. Такие конструкции могут применяться не только в описываемом здесь случае. В частности, если в запросе используются длинные наименования таблиц, то можно в предложении FROM для каждой такой таблицы указать синоним, состоящий всего из одной или нескольких букв, и дальше пользоваться исключительно синонимами. Обычно этим пользуются в СУБД, где приходится вводить текст оператора SQL вручную (нет такого удобного средства как конструктор запросов), или существуют серьезные ограничения на длину SQL-оператора.