SQL: несколько Join к одной таблице / связи

Бывают случаи, когда нужно создать несколько Join запросов к одной таблице, то есть когда у нас есть много элементов в таблице ссылающихся на тот же внешний ключ. Это не сложная задача, если вы имели дело с этим раньше, но может немного сбить с толку новых пользователей БД. Вот почему я создал эту статью — чтобы объяснить, как правильно проектировать и связывать таблицы в SQL.

Пример двойного ключа — команды и игры

Как простой пример возьмем две команды играющие в игру, например футбол. Каждая команда будет иметь уникальный столбец в таблице. Давайте рассмотрим следующую простую таблицу в качестве примера того, как может выглядеть команда (примеры в этой статье написаны для MySQL, но по аналогии можно делать для любых ANSI-92 совместимых БД):

CREATE TABLE team (
 id INT NOT NULL AUTO_INCREMENT,
 name VARCHAR(255) NOT NULL,
 PRIMARY KEY (id)
 ) TYPE=InnoDB;

Пока все просто… Это основа базы данных. Что ж, давайте перейдем к играм.

Две ссылки

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

CREATE TABLE game (
    id INT NOT NULL AUTO_INCREMENT,
    home_team_id INT NOT NULL REFERENCES team(id),
    guest_team_id INT NOT NULL REFERENCES team(id),
    home_score INT NULL,
    guest_score INT NULL,
    game_date DATE NOT NULL,
    PRIMARY KEY (id)
) TYPE=InnoDB;

Дважды соединенные

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

SELECT
    g.*,
    t1.name AS home_team_name,
    t2.name AS guest_team_name
FROM
    game AS g
    INNER JOIN team AS t1 ON g.home_team_id = t1.id
    INNER JOIN team AS t2 ON g.guest_team_id = t2.id;
ORDER by game_date DESC

Обратная ссылка

Поиск расписания одной команды включает в себя объединение домашних и выездных игр, с созданием идентификатора метаданных места встречи.

SELECT * FROM (
    SELECT
        t1.*,
        g.id AS game_id,
        g.game_date,
        t2.id AS opponent_id,
        t2.name AS opponent_name,
        'home' AS venue
    FROM
        game AS g
        INNER JOIN team AS t1 ON g.home_team_id = t1.id
        INNER JOIN team AS t2 ON g.guest_team_id = t2.id
    UNION ALL
    SELECT
        t1.*,
        g.id AS game_id,
        g.game_date,
        t2.id AS opponent_id,
        t2.name AS opponent_name,
        'away' AS venue
    FROM
        game AS g
        INNER JOIN team AS t1 ON g.guest_team_id = t1.id
        INNER JOIN team AS t2 ON g.home_team_id = t2.id
) AS schedule
WHERE schedule.id = 1
ORDER BY schedule.game_date ASC

Вот и все! Углубляйтесь, и получайте удовольствие!

Источник: http://www.transio.com/content/sql-multiple-joins-single-table-relationship

2 комментариев

  1. кажется в 3 примере там точка с запятой лишняя
    запросы проверялись на валидность?

  2. Если быть точнее, в дважды соединенные

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

*

Можно использовать следующие HTML-теги и атрибуты: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>