Cant bind correlated subquery with WHERE clause?

[1DMF]

Hi,

I'm trying to join some data with a correlated subquery from a linked server table.

I have...

SELECT CompanyName,FirstName,LastName,[Membership Number]
FROM dbo.Contacts c
LEFT OUTER JOIN (
			SELECT TOP 1 Adv_MemNo,Sub_Date 
			FROM [Members].[Members_Live].[dbo].Business_Register b
			WHERE b.Adv_MemNo = c.[Membership Number]
			ORDER BY Sub_Date ASC
			) AS FirstNBCS
			ON c.[Membership Number] = FirstNBCS.Adv_MemNo

But it won't run with

The multi-part identifier "c.Membership Number" could not be bound.

Any ideas?

Thanks,
1DMF.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[1DMF]

Well I changed it to

SELECT CompanyName,FirstName,LastName,[Membership Number],(
			SELECT TOP 1 Sub_Date 
			FROM [Members].[Members_Live].[dbo].Business_Register b
			WHERE b.Adv_MemNo = c.[Membership Number]
			ORDER BY Sub_Date ASC
			) AS FirstNBCS
FROM dbo.Contacts c

And it works, so am I not able to use correlated column names in the FROM clause, only the SELECT clause?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[1DMF]

Well I changed it to

SELECT CompanyName,FirstName,LastName,[Membership Number],(
			SELECT TOP 1 Sub_Date 
			FROM [Members].[Members_Live].[dbo].Business_Register b
			WHERE b.Adv_MemNo = c.[Membership Number]
			ORDER BY Sub_Date ASC
			) AS FirstNBCS
FROM dbo.Contacts c

And it works, so am I not able to use correlated column names in the FROM clause, only the SELECT clause?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[1DMF]

I had a look at Wiki

http://en.wikipedia.org/wiki/Correlated_subquery

and it seems to use it in the WHERE or as I have found is OK in the SELECT clause but not the FROM, is that due to the processing order of a SQL statement and as the FROM clause is processed first it needs to create the Cartesian product of all tables in the FROM clause before it can process any correlation?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[1DMF]

oops just noticed my thread title say's 'WHERE' and I meant 'FROM' d'oh

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads