Because you are using the same column and table in the subquery, then you simply make the subquery redundant.
SELECT SomeField, AccountName FROM AccountInfo WHERE AccountID=123456
will get you the accurate result. The issue in your query is that you may have more than 1 record in the accountInfo table with a given CustomerID so you will throw an error in that the subquery can have multiple results when it can only have one. If you actually want more than one result then you could do something like this, but I'm not entirely sure the inner join will be valid like this. Something you can try, though.
FROM tblAccountInfo t1
INNERJOIN tblAcocuntInfo t2 ON t2.CustomerID = t1.CustomerID
WHERE t1.AccountID = 123456
To answer your question, there will be no "circular problem". If you run this query, you should see that it works fine. What allows you to have the same table multiple times in a single query is aliasing, which you are doing (you are aliasing tblAccountInfo as tb1 and tb2).
Like others have mentioned, your query doesn't make sense as is. If, however, there can be multiple records with the same CustomerID, this would make sense (notice the TOP 1):
FROM tblAccountInfo AS tb2
tb2.CustomerID = tb1.CustomerID
) AS strAccountName
FROM tblAccountInfo AS tb1
tb1.AccountID = 123456
But then, you also mentioned an aggregate function (such as MAX) in a reply above, which would accomplish much the same. If CustomerID is a unique field, then an aggregate or TOP 1 would be unnecessary, but then so would the subquery.
Thanks for all the replies and suggestions. as it stands the table structure does reveal different AccountNames when queried on different columns (AccountID, CustomerID). And yes, that seems redundant because for case of one record having unique set of AccountID, CustomerID, and AccountName it is obvious. For case of many records with mixed combinations of those 3 columns, I am not guaranteed the AccountName is what I want. I think I'm only concerned if AccountName is not NULL. Thanks!
Last Visit: 31-Dec-99 19:00 Last Update: 23-Mar-17 3:07