Многослойный идентификатор не мог быть связан на SQL Server 2008
Я не полагаю, что существует установленная "лучшая практика".
я поместил свои тесты в другой каталог за пределами кода приложения. Я тогда добавляю каталог главного приложения к sys.path (разрешающий Вам импортировать модули отовсюду) в моем сценарии исполнителя тестов (который делает некоторый другой материал также) прежде, чем запустить все тесты. Таким образом, я никогда не должен удалять тестовый каталог из основного кода, когда я выпускаю его, экономя мне время и усилие, если очень крошечная сумма.
2 ответа
The code example you showed had ellipses and I believe it is what is in the ellipses that are causing the trouble.
You have:
SELECT R.[ID], C.[name] AS [company], ...
FROM [requests] AS R, ...
LEFT OUTER JOIN [companies] AS C
ON R.[company_id] = S.ID
Let's say that is something like:
SELECT R.[ID], C.[name] AS [company], X.Field
FROM [requests] AS R, [eXample] as X
LEFT OUTER JOIN [companies] AS C
ON R.[company_id] = S.ID
WHERE X.[request_id] = R.ID
In other words the mixing of pre-ANSI 92 inner join syntax with ANSI 92 outer join syntax. Testing on SQL Server 2005, it appears that the alias R for requests is not seen past the comma that separates R from ... in your example, and [eXample] as X in mine. The following however did work:
SELECT R.[ID], C.[name] AS [company], X.Field
FROM [eXample] as X, [requests] AS R
-- Requests and companies on the same side of the comma
LEFT OUTER JOIN [companies] AS C
ON R.[company_id] = S.ID
WHERE X.[request_id] = R.ID
or
SELECT R.[ID], C.[name] AS [company], X.Field
FROM [requests] AS R LEFT OUTER JOIN [companies] AS C
ON R.[company_id] = S.ID, [eXample] as X
WHERE X.[request_id] = R.ID
-- Yuck, I would hate to find this. Not at all sure from reading
-- the code how it would work.
or my favorite, because I like ANSI 92 join syntax:
SELECT R.[ID], C.[name] AS [company], X.Field
FROM [requests] AS R
INNER JOIN [eXample] as X ON X.[request_id] = R.ID
LEFT OUTER JOIN [companies] AS C ON R.[company_id] = S.ID
Я думаю, вы хотите:
SELECT R.[ID], ISNULL(C.[name], '') AS [company]
FROM [requests] AS R
LEFT OUTER JOIN [companies] AS C
ON R.[company_id] = C.ID
РЕДАКТИРОВАТЬ: См. Комментарии, требуется левое соединение ...
Мне также кажется, что нет необходимости в левом соединении, поэтому вы можно переписать как:
SELECT R.[ID], C.[name] AS [company]
FROM [requests] AS R
JOIN [companies] AS C
ON R.[company_id] = C.ID