Соединение таблиц для создания многотабличного запроса

Перед соединением таблиц необходимо четко представлять, какие поля связаны общими значениями. В режиме запроса по образцу выводятся четыре типа соединений:

Внутреннее соединение (или эквисоединение) обычно используется при создании запросов на выборку. Результирующее множество запроса содержит записи одной таблицы, имеющие совпадающие значения в связанных полях другой (where поле1 = поле2). В большинстве случаев соединения основываются на уникальном значении поля первичного ключа в одной таблице и значениями поля внешнего ключа в другой таблице, если таблицы связаны отношением "один-ко-многим". Если в таблице "многие" записи с заданной величиной отсутствуют, то соответствующие записи таблицы "один" в результирующее множество не включаются. Access автоматически создает соединения между таблицами, если в них имеются поля с одинаковыми именами, одно из которых является ключевым, либо если соединение было задано в окне связей.

Внешнее соединение используется для создания новой таблицы, которая содержит записи, исключая повторяющиеся, связанные поля которых совпадают. Внешнее соединение позволяет вывести данные одной из таблиц независимо от того, имеются ли соответствующие записи в другой таблице.
Рекурсивное соединение связывает данные в одной таблице. Создание этого типа соединения выполняется путем добавления в запрос копии таблицы (Access назначает псевдоним для копии) и связывания полей идентичных таблиц.
Соединение по отношению (или тэта-соединение) связывает данные некоторым отношением (это может быть любое отношение, исключая равенство). Соединение по отношению выполняется с помощью предложения where, а не с помощью ключевого слова SQL join. Кроме того, в окне конструктора запросов соединения по отношению не выводятся, также как они не отображаются в окне схемы данных.
Важно отметить, что далеко не все возможности ACCESS SQL могут быть реализованы в окне запроса по образцу.

Создание внутреннего соединения по одному полю

Соединения, в которых участвует по одному полю из двух разных таблиц, называются внутренними соединениями по одному полю. В большинстве реляционных баз данных такие соединения реализуются на основе отношений "один-ко-многим". При разработке базы данных, в которой предполагается использовать для всех запросов только внутренние соединения по одному столбцу, необходимо придерживаться следующих основных правил:

Каждая таблица "один" должна иметь первичный ключ, совпадений значений которого не допускается в целях обеспечения целостности данных. Access автоматически устанавливает отсутствие повторений значений поля или полей первичного ключа в таблице.

Отношение "многие-ко-многим", например, связь между таблицами "Заказы" и "Товары", реализуется на основе промежуточной таблицы (в данном случае таблицы "Заказано"), которая связана с одной таблицей отношением "один-ко-многим" (таблица "Заказы" с таблицей "Заказано"), а с другой таблицей отношением "многие-к-одному" (таблица "Заказано" с таблицей "Товары").

Повторяющиеся данные, там где это только возможно, необходимо извлечь в новую таблицу, имеющую первичный ключ, совпадений значений которого не допускается, и связать с таблицей, из которой эти данные были получены отношением "один-ко-многим". Чтобы однозначно определить извлеченные данные, часто требуется использовать первичный ключ, состоящий из нескольких полей, т. к. одно поле может включать повторяющиеся данные. Однако комбинация значений ключевых полей должна быть уникальной.

Все соединения в базе данных "Борей", обозначенные линиями, которые соединяют имена полей, являются внутренними соединениями по одному полю, реализованные на основе отношений "один-ко-многим". В Access используются зарезервированные слова ANSI SQL-92 inner join для обозначения внутренних соединений, а слова left join или right join для указания внешних соединений.

Типичным результатом использования запроса, основанного на внутреннем соединении, является множество имен клиентов и их адресов вместе с размещенными ими заказами. Например, может потребоваться создать простой отчет, в котором необходимо вывести имя клиента, номер и дату заказа, а также количество товара.

 

Задание 2.15.

Построить запрос, в котором имеется внутреннее соединение одной таблицы с другой отношением "один-ко-многим", и связать имена клиентов с размещенными ими заказами:

1. Раскройте вкладку "Запросы" окна "База данных", а затем нажмите кнопку "Создать" для создания нового запроса. В появившемся диалоговом окне "Новый запрос" выделите элемент "Конструктор" и нажмите кнопку ОК.

2. Выберите таблицу "Клиенты" и нажмите кнопку "Добавить" чтобы добавить ее в запрос. Access отображает список полей таблицы "Клиенты".
3. Добавьте также таблицу "Заказы", а затем нажмите кнопку "Закрыть". Access добавляет список полей таблицы "Заказы", а также линию, обозначающую соединение полей "КодКлиента" двух таблиц. Соединение создается автоматически, т. к. поле "КодКлиента" является ключевым в таблице "Клиенты" и в таблице "Заказы" найдено поле с тем же именем (внешний ключ).

4.Чтобы отыскать заказы, размещенные клиентом, выберите поле "Название" в таблице "Клиенты" и перетащите поле в строку "Поле" первого столбца бланка запроса.

5.Выберите поле "КодЗаказа" в таблице "Заказы" и перетащите поле в строку "Поле" второго столбца. Перетащите поле "ДатаРазмещения" в третий столбец.

6.Нажмите кнопку "Запуск" либо кнопку "Режим таблицы" для вывода результирующего множества запроса.

 

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

 

Задание 2.16.

Выведите в запросе данные косвенно связанных записей.

1. Вернитесь к последнему запросу или создайте новый, введя туда таблицу «Клиенты».
2. Удалите в режиме конструктора запроса все столбцы, исключая "Название".
3. Нажмите кнопку "Добавить таблицу" на панели инструментов либо выберите команду "Запрос, Добавить таблицу" и добавьте в запрос таблицы "Заказы", "Заказано", "Товары" и "Типы", а затем нажмите кнопку "Закрыть" диалогового окна "Добавление таблицы".

4. Access автоматически связывает таблицы "Заказы" и "Типы", соединяя промежуточные таблицы с помощью поля первичного ключа в одной таблице и поля внешнего ключа с идентичным именем в другой.
5. Перетащите поле "Категория" из списка полей таблицы "Типы" на строку "Поле" второго столбца бланка запроса.

Проанализируйте эквивалентную созданному запросу инструкцию SQL. Соединения таблиц задаются операцией inner join..on.... Косвенные соединения реализуются на основе выражения inner join...on...on...

SELECT DISTINCTROW Клиенты.Название, Типы.Категория
FROM Типы INNER JOIN (Товары INNER JOIN ((Клиенты INNER JOIN Заказы ON Клиенты.КодКлиента = Заказы.КодКлиента) INNER JOIN Заказано ON Заказы.КодЗаказа = Заказано.КодЗаказа) ON Товары.КодТовара = Заказано.КодТовара) ON Типы.КодТипа = Товары.КодТипа;

Создание внутреннего соединения по нескольким полям

Между двумя таблицами может быть задано несколько соединений. Например, допустим, что требуется вывести имена клиентов, у которых совпадают официальный адрес и адрес доставки. Адрес клиента хранится в поле "Адрес" таблицы "Клиенты", а адрес доставки — в поле "АдресПолучателя" таблицы "Заказы". Таким образом, необходимо сопоставить поля "КодКлиента" в обеих таблицах, а также поля "Адрес" и "АдресПолучателя". Для этого требуется использовать внутреннее соединение по нескольким полям.

 

Задание 2.17.

Выведите имена клиентов, официальный адрес и адрес доставки которых совпадают.
1. Создайте новый запрос.

2.Добавьте в запрос таблицы "Клиенты" и "Заказы".

3. Выберите в списке полей таблицы "Клиенты" поле "Адрес" и перетащите его на поле "АдресПолучателя" таблицы "Заказы". Это приводит к созданию соединения полей Адрес и АдресПолучателя. Линия, обозначающая связь, имеет с двух сторон точки, которые указывают на то, что соединение выполнено между полями, связь которых в схеме данных не задана, их имена не совпадают и они не являются первичными ключами.
4. Перетащите поля "Название" и "Адрес" таблицы "Клиенты", а затем поле "АдресПолучателя" таблицы "Заказы" на бланк запроса.
5. Задайте сортировку по возрастанию в столбце "Название.
6. Запустите запрос.

7. Чтобы подавить вывод одинаковых строк, необходимо изменить значение свойства "Уникальные значения" в бланке свойств запроса. Для отображения бланка свойств нажмите кнопку "Конструктор", а затем кнопку "Свойства" на панели инструментов. Если заголовком бланка является строка "Свойства списка полей" или "Свойства поля", щелкните по пустому месту, чтобы вывести свойства запроса. Установить «Уникальное значение» на «ДА». Кроме того, можно щелкнуть правой кнопкой мыши по свободной области верхней части окна запроса и выбрать в контекстном меню команду "Свойства".

8. Нажмите на панели инструментов кнопку "Запуск". Результирующее множество не содержит повторяющихся записей. Обратите внимание на количество записей.

9. Перейдите в режим SQL. Обратите внимание на то, что ключевое слово distinct инструкции select исключает записи, которые содержат повторяющиеся значения в отобранных полях. Исследуйте как влияет изменение свойства запроса "Уникальные значения" и "Уникальные записи" на оператор DISTINCT SELECT-предложения.

SELECT DISTINCT Клиенты.Название, Клиенты.Адрес, Заказы.АдресПолучателя