Correlated Subquery

Can you use a correlated subquery in a join expression, when you reference
the table your joining to?Hi Ben
An example of what you are trying to do would be useful! See
http://www.aspfaq.com/etiquette.asp?id=5006
I assume that you are soing something like:
SELECT a.col1, a.col2, d.col1
FROM tablea a
JOIN ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1 ) d where a.col1 = d.col1
and want to do
SELECT a.col1, a.col2, d.col1
FROM tablea a
JOIN ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1
WHERE a.col1 = c.col1 ) d
Then the answer is no.
In SQL 2005 there is the CROSS APPLY operator where you could write your
derived table as a function
CREATE FUNCTION dbo.fn_derivedtbl(@.col1 AS INT) RETURNS TABLE
AS
RETURNS ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1
WHERE c.col1 = @.col1 )
SELECT a.col1, a.col2, d.col1
FROM tablea a
CROSS APPLY dbo.fn_derivedtbl(a.col1) AS d
N.B None of the scripts are tested!
John
"Ben UK" wrote:

> Can you use a correlated subquery in a join expression, when you reference
> the table your joining to?|||To my knowledge, no, you cannot. Correlated subqueries can be used only in
the Select and the Where statements.
However, if you make a new post explaining exactly what you are trying to
achieve, maybe someone will be able to provide you with an alternate
solution.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:E2A87171-A1E4-4209-A056-E577B7A8326C@.microsoft.com...
> Can you use a correlated subquery in a join expression, when you reference
> the table your joining to?