SQL Server View

[ashab02]

Hello

I am currently writing a view in sql server using the front end view building tool and I am trying to within the criteria use the 'IN' Statement which then does a sub query as follows 'IN (SELECT TOP 1 DATE FROM CUSTOMER MA WHERE DATE >'12/05/2006' AND CUSTOMERCODE = MA.CUSTOMER CODE). It is basically needs to join on its self but it keeps bring an error of :
'The column prefix 'MA' does not match with a table or alias name used in the query'

Can anyone help?

 

[hmckillop]

Two points, firstly you dont need to use IN when your inner statement is only returning 1 row.
Second, ensure you alias all tables in outer query and reference in inner query.

e/g

SELECT * FROM MyOuterTable tb1
IN (SELECT TOP 1 DATE FROM CUSTOMER MA WHERE MA.DATE >'12/05/2006' AND TB1.CUSTOMERCODE = MA.CUSTOMER CODE)

"I'm living so far beyond my income that we may almost be said to be living apart

 

[SQLBill]

One correction to hmckillop's code......the WHERE is missing:

SELECT * FROM MyOuterTable tb1
WHERE DATE IN (SELECT TOP 1 DATE FROM CUSTOMER MA WHERE MA.DATE >'12/05/2006' AND TB1.CUSTOMERCODE = MA.CUSTOMER CODE)

-SQLBill

Posting advice: FAQ481-4875