В EXISTS мой JOIN ON может использовать значение из исходного выбора

Question

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

У меня есть следующие таблицы и (применимые) поля:

Docs: DocNo, FileNo
DocAccess: DocNo, UserTypeWithAccess
FileUsers: FileNo, UserType, UserNo 

У меня есть следующий запрос:

SELECT Docs.* 
FROM Docs
WHERE DocNo = 1000
  AND EXISTS (
         SELECT * FROM DocAccess
         LEFT JOIN FileUsers
           ON FileUsers.UserType = DocAccess.UserTypeWithAccess 
           AND FileUsers.FileNo = Docs.FileNo /* Errors here */
         WHERE DocAccess.UserNo = 2000 )

Проблема в том, что в Exists Select он не распознает документы (в Docs.FileNo) как допустимые таблицы. Если я перенесу второй аргумент в предложение where, оно будет работать, но я бы предпочел ограничить начальное объединение, а не отфильтровать их по факту.

Я могу обойти это несколькими способами, но, похоже, это будет лучше. Что-то мне здесь не хватает? Или это просто не разрешено?

Answer 1

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

Тем не менее, вам не нужно беспокоиться о том, где вы помещаете конкретное предложение. SQL - это описательный язык, а не процедурный язык. Целью SQL является описание вывода. Механизм SQL, синтаксический анализатор и компилятор должны выбирать наиболее оптимальный путь выполнения. Не всегда правда. Но переместите условие в where оговорка и не беспокойся об этом.

Answer 2

Вы должны использовать псевдонимы, чтобы это работало:

SELECT
  doc.* 
FROM 
  Docs doc
WHERE 
      doc.DocNo = 1000
  AND EXISTS (
SELECT 
  * 
FROM 
  DocAccess acc
LEFT OUTER JOIN 
  FileUsers usr
ON 
      usr.UserType = acc.UserTypeWithAccess 
  AND usr.FileNo   = doc.FileNo
WHERE 
  acc.UserNo = 2000
)

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

Если вы хотите ограничить вывод только одной строкой, вы можете использовать TOP 1:

SELECT TOP 1
  doc.* 
FROM 
  Docs doc
INNER JOIN
  FileUsers usr
ON  
  usr.FileNo = doc.FileNo
INNER JOIN
  DocAccess acc
ON
  acc.UserTypeWithAccess = usr.UserType
WHERE 
      doc.DocNo  = 1000
  AND acc.UserNo = 2000

Конечно, второй запрос работает немного иначе, чем первый (оба соединения являются внутренними). В зависимости от модели данных вы можете даже оставить TOP 1 из этого запроса.

Answer 3

Мне не понятно, зачем вам вообще присоединяться к FileUsers в вашем подзапросе? Какова цель и идея запроса (простым английским языком)?

В любом случае, если вам нужно объединиться с FileUsers, я предлагаю использовать внутреннее объединение и переместить второй фильтр в условие WHERE. Я не думаю, что вы можете использовать его в состоянии JOIN в подзапросе - по крайней мере, я никогда не видел, чтобы он использовал этот способ раньше. Я считаю, что вы можете соотносить только через предложение WHERE.