referring to subselect with alias in where clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

referring to subselect with alias in where clause

Author

Topic

aleceiffel1066
Starting Member

2 Posts

Posted - 2009-05-18 : 09:54:28

hi there, newbie question about the following query:

select
tasks.task_id as taskId,
tasks.task_name as taskName,
tasks.task_short_description as taskShortDescription,
(select employees.employee_name from employees where tasks.creator_employee_id = employees.employee_id) as taskCreatorEmployee,
work_time_entries.work_entry_id as workEntryId,
work_time_entries.work_start_time as workStartTime,
work_time_entries.work_end_time as workEndTime,
work_time_entries.work_units as workUnits,
employees.employee_name as workTimeEmployeeName

from tasks
inner join work_time_entries on work_time_entries.task_id = tasks.task_id
inner join employees on employees.employee_id = work_time_entries.employee_id

where taskCreatorEmployee = 'name'

Doing this doesn't work, is there a way to get at the data I am looking for when I only know the name of the task creator employee, not the employee id?

thanks so much,

alec

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-18 : 12:02:13

I cannot remember if you can use an alias in a WHERE clause like that under any version of SQL. But, you can certainly join to Employees again:

select 
	tasks.task_id as taskId,
	tasks.task_name as taskName,
	tasks.task_short_description as taskShortDescription,
	TaskCreator.employee_name  as taskCreatorEmployee,
	work_time_entries.work_entry_id as workEntryId,
	work_time_entries.work_start_time as workStartTime,
	work_time_entries.work_end_time as workEndTime,
	work_time_entries.work_units as workUnits,
	employees.employee_name as workTimeEmployeeName
from 
	tasks
inner join 
	work_time_entries 
	on work_time_entries.task_id = tasks.task_id
inner join 
	employees 
	on employees.employee_id = work_time_entries.employee_id
INNER JOIN
	employees AS TaskCreator
	ON tasks.creator_employee_id = TaskCreator.employee_id
WHERE
	TaskCreator.employee_name = 'name'

aleceiffel1066
Starting Member

2 Posts

Posted - 2009-05-18 : 14:55:47

thanks so much Lamprey, this works famously!

-alec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources