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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-08-23 : 10:00:58
|
| arif writes "hey, here is query from guru's guide to T-SQL, select t1.employee,dateadd(mi,1,t1.timeout),dateadd(mi,-1,t1.timein),datediff(mi,t1.timeout,t2.timein)from timeclock t1 join timeclock t2 on t1.employee=t2.employeewhere (dateadd(mi,1,timeout))= (select max(dateadd(mi,1,t3.timeout)) from timeclock t3 where t3.employee=t1.employeeand dateadd(mi,1,t3.timeout)<= dateadd(mi,-1,timein)).will you please explain what is the correlated query for,I spent a lot of time to dig into it , but i was not able to understand, infact he is the guru.Thanx in advance" |
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-08-23 : 11:33:31
|
| Sure-The sub query in this case is located in the WHERE clause of the outer query.This sub query is called a CORRELATED subquery because it references an aliased table from the outer query. In this case, the subquery restricts the outer query to the maximum "timeout" value for each individual employee.Look at the WHERE clause of the subquery. "t3.employee=t1.employee " directly references the T1 alias, which is only defined in the outer query.The guru says this syntax results in a traditional looping construct. A MAX(timeout) request in the outer query could return a max value for a single employee, or for all employees....but not for each and every individual employee. The syntax of the correlated subquery enables one to do just that.see pages 141-147 of Guru's guide.Edited by - kevin snow on 08/23/2002 11:45:17 |
 |
|
|
|
|
|
|
|
complex query