Как получить соответствующие данные из другой таблицы SQL для двух разных столбцов: Inner Join и/или Union?

У меня есть две таблицы в MS Access, которые отслеживают фасилитаторы классов и классы, которые они облегчают. Эти две таблицы структурированы следующим образом:

tbl_facilitators

facilID -> a unique autonumber to keep track of individual teachers
facilLname -> the Last name of the facilitator
facilFname -> the First name of the facilitator

tbl_facilitatorClasses

classID -> a unique autonumber to keep track of individual classes
className -> the name of the class (science, math, etc)
primeFacil -> the facilID from the first table of a teacher who is primary facilitator
secondFacil -> the facilID  from the first table of another teacher who is backup facilitator

Я не могу понять, как написать Inner Join, который подтягивает результаты в этом формате:

Column 1:  Class Name
Column 2:  Primary Facilitator Last Name
Column 3:  Primary Facilitator First Name
Column 4:  Secondary Facilitator Last Name
Column 5:  Secondary Facilitator First Name

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

Это моя работа Inner Join:

SELECT tbl_facilitatorClasses.className,
    tbl_facilitators.facilLname, tbl_facilitators.facilFname
FROM tbl_facilitatorClasses
INNER JOIN tbl_facilitators
ON tbl_facilitatorClasses.primeFacil = tbl_facilitators.facilID;

Из отчаяния я также пробовал Союз, но это не сработало, как я надеялся. Ваша помощь очень ценится. Я действительно изо всех сил пытаюсь добиться прогресса на этом этапе. Я не часто работаю с SQL.

Решение

Благодаря @philipxy я придумал следующий запрос, который закончился:

SELECT tblCLS.className,
    tblP.facilLname, tblP.facilFname, tblS.facilLname, tblS.facilFname
FROM (tbl_facilitatorClasses AS tblCLS
INNER JOIN tbl_facilitators AS tblP
ON tblCLS.primeFacil=tblP.facilID)
INNER JOIN tbl_facilitators AS tblS
ON tblCLS.secondFacil=tblS.facilID;

При выполнении нескольких внутренних подключений в MS Access необходимы скобки... Как описано в этом другом сообщении.

Ответы

Ответ 1

Каждая базовая таблица имеет шаблон оператора, aka predicate, параметризованный именами столбцов, по которым мы помещаем строку или оставляем ее. Мы можем использовать сокращенное выражение для предиката, похожее на его объявление SQL.

// facilitator [facilID] is named [facilFname] [facilLname]
facilitator(facilID,facilLname,facilFname)
// class [classID] named [className] has prime [primeFacil] & backup [secondFacil]
class(classID,className,primeFacil,secondFacil)

Включение строки в предикат дает утверждение aka. Строки, которые делают истинное предложение, идут в таблицу, а строки, которые делают ложное предложение, остаются в силе. (Таким образом, таблица утверждает предложение каждой текущей строки и не указывает на предложение каждой отсутствующей строки.)

// facilitator f1 is named Jane Doe
facilitator(f1,'Jane','Doe')
// class c1 named CSC101 has prime f1 & backup f8
class(c1,'CSC101',f1,f8)

Но каждое значение выражения таблицы имеет предикат в своем выражении. SQL сконструирован так, что если таблицы T и U содержат (NULL-свободные не дубликаты) строки, где T (...) и U (...) (соответственно), то:

  • T CROSS JOIN U содержит строки, где T (...) AND U (...)
  • T INNER JOIN U ON condition содержит строки, где T (...) AND U (...) AND condition
  • T LEFT JOIN U ON condition содержит строки где (для U-единственных столбцов U1,...)
        T (...) И U (...) И условие OR T (...) AND NOT (U (...) AND condition) И U1 IS NULL AND...
  • T WHERE condition содержит строки, где T (...) AND condition
  • T INTERSECT U содержит строки, где T (...) AND U (...)
  • T UNION U содержит строки, где T (...) OR U (...)
  • T EXCEPT U содержит строки, где T (...) AND NOT U (...)
  • SELECT DISTINCT * FROM T содержит строки, где T (...)
  • SELECT DISTINCT columns to keep FROM T содержит строки, где СУЩЕСТВУЕТ СУЩЕСТВУЮЩИЕ столбцы, ТАК ЧТО Т (...)
  • VALUES (C1, C2, ...)(( v1 , v2 , ...), ...) содержит строки, где C1 = v1 AND C2 = v2 AND... OR...

также:

  • (...) IN T означает T (...)
  • scalar = T означает T (скалярный)
  • T (..., X,...) И X = Y означает T (..., Y,...) И X = Y

Таким образом, для запроса мы находим способ формулировки предиката для строк, которые мы хотим на естественном языке, с использованием предикатов базовой таблицы, затем в сокращении с использованием предикатов базовой таблицы, а затем в SQL с использованием имен базовых таблиц (плюс условия, где это необходимо). Если нам нужно дважды упомянуть таблицу, мы дадим ей псевдонимы.

// natural language
THERE EXISTS classID,primeFacil,secondFacil SUCH THAT
    class [classID] named [className] has prime [primeFacil] & backup [secondFacil]
AND facilitator [primeFacil] is named [pf.facilFname] [pf.facilLname]
AND facilitator [secondFacil] is named [sf.facilFname] [sf.facilLname]

// shorthand
THERE EXISTS classID,primeFacil,secondFacil SUCH THAT
    class(classID,className,primeFacil,secondFacil)
AND facilitator(pf.facilID,pf.facilLname,pf.facilFname)
AND pf.facilID = primeFacil
AND facilitator(sf.facilID,sf.facilLname,sf.facilFname)
AND sf.facilID = secondFacil

// table names & (MS Access) SQL
SELECT className,pf.facilLname,pf.facilFname,sf.facilLname,sf.facilFname
FROM (class JOIN facilitator AS pf ON pf.facilID = primeFacil)
JOIN facilitator AS sf ON sf.facilID = secondFacil

OUTER JOIN будет использоваться, когда класс не всегда имеет обоих фасилитаторов, или что-то не всегда имеет все имена. (Т.е. если столбец может быть NULL.) Но вы не указали конкретные предикаты для вашей базовой таблицы и запроса или бизнес-правил, когда вещи могут быть NULL, поэтому я не принял NULL.

(в скобках Re MS Access JOIN см. это из SO и это из MS.)

Ответ 2

Просто сделайте дополнительное соединение для вспомогательного фасилитатора (и, пожалуйста, используйте псевдонимы таблицы!):

SELECT fc.className, f1.facilLname, f2.facilFname
FROM tbl_facilitatorClasses fc
INNER JOIN tbl_facilitators f1 ON fc.primeFacil = f1.facilID
INNER JOIN tbl_facilitators f2 ON fc.secondFacil = f2.facilID;

Ответ 3

Я сделал бы это, как указано выше, дважды вставлюсь в таблицу tbl_facilitators, но вы можете убедиться, что каждый класс действительно требует второго фасилитатора, поскольку второе соединение должно быть внешним соединением. Действительно, было бы безопаснее предположить, что это не обязательное поле.