SQL Server 2005 how to use union syntax?

Question

I have this query

select c.RequestedByEmployeeID 
from Company c with (NOLOCK)

which works, but when I try this, it fails

select 
    c.RequestedByEmployeeID as ResponsibleLawyerID

union

select 
    c.RequestedByEmployeeID as ResponsibleLawyerID
from
    Company c with (NOLOCK)
order by
    c.CompanyID

with this error:

Msg 107, Level 16, State 2, Line 44
The column prefix 'c' does not match with a table name or alias name used in the query.

Msg 107, Level 16, State 2, Line 44
The column prefix 'c' does not match with a table name or alias name used in the query.

How can I fix it?

Thanks

Answer 1

Just don't use the alias and repeat the FROM clause:

select c.CompanyId, c.RequestedByEmployeeID as ResponsibleLawyerID
from Company c
union
select c.CompanyId, c.RequestedByEmployeeID as ResponsibleLawyerID
from Company c
order by CompanyID;

Note that you need to select the column. If you don't want to select the column but you want to order by it, then one solution is a subquery. Your query is basically non-sensical (selecting the same column), so adding the company seems reasonable.

The order by refers to the result of the union, not to a particular subquery. So, an alias is not appropriate.

The use of union means that you don't want duplicates, so the database incurs a performance penalty for removing them. If you don't care, then use union all.