I have 2 tables
requests
(ID
, company_id
, amount
)
companies
(ID
, name
)
with FK constraint (requests.company_id
-> companies.id
)
requests.company
can be NULL
I need to get all requests and replace company_id
with appropriated company name
or left it blank if no company was specified.
I have next query:
SELECT R.[ID], C.[name] AS [company], R.[amount], ...
FROM [requests] AS R, [companies] AS C, ...
WHERE R.[company_id] = C.[ID]
and it's working fine until a NULL into company
field.
I tried to do next:
SELECT R.[ID], C.[name] AS [company], ...
FROM [requests] AS R, ...
LEFT OUTER JOIN [companies] AS C
ON R.[company_id] = S.ID
But got
The multi-part identifier "R.company_id" could not be bound
And the same errors on fields in ON
clause shifting. What am I doing wrong?
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
I think you want:
SELECT R.[ID], ISNULL(C.[name], '') AS [company]
FROM [requests] AS R
LEFT OUTER JOIN [companies] AS C
ON R.[company_id] = C.ID
EDIT: See comments, the left join is needed ...
It also appears to me that there's no need for the left join, so you can re-write as:
SELECT R.[ID], C.[name] AS [company]
FROM [requests] AS R
JOIN [companies] AS C
ON R.[company_id] = C.ID
Changing the table order in comma makes it work in sql server 2005,2008 and 2012
e.g
Let's say you have a query like below which will fail in sql 2005 and above:
SELECT t1.*,t2.*, t3.*
FROM table1 AS t1, table2 as t2
LEFT OUTER JOIN table3 AS t3
ON t1.id = t3.id
WHERE t1.id = t2.id
The query will work in sql 2005 and above if you rewrite the query and change the order of the table in the commas before the join. e.g
SELECT t1.*,t2.*, t3.*
FROM table2 as t2, table1 as t1
LEFT OUTER JOIN table3 AS t3
ON t1.id = t3.id
WHERE t1.id = t2.id