subquery using 1 table

[john john mackey]

i'm not sure if this is a Circular Reference problem or not. i have the following SQL query

SELECT tb1.SomeField,
(SELECT tb2.AccountName
FROM tblAccountInfo tb2
WHERE tb2.CustomerID = tb1.CustomerID) AS strAccountName
FROM tblAccountInfo tb1
WHERE tb1.AccountID = 123456

Since i'm using the same table in the subquery, is this a problem? If it is a circular problem, how could i correct this? Thanks, JJ

[fjdiewornncalwe]

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.

SELECT t1.SomeField,
t2.AccountName
FROM tblAccountInfo t1
INNER JOIN tblAcocuntInfo t2 ON t2.CustomerID = t1.CustomerID
WHERE t1.AccountID = 123456

I wasn't, now I am, then I won't be anymore.

[john john mackey]

I think i need the construct i initially posed, but there may be the case where the result may return more than one result - in which case i could handle with MAX() or a simple aggregate. When i first call tblAccountInfo with AccountID, it gives me 1 record in tblAccountInfo with a CustomerID. When i call tblAccountInfo using CustomerID, it will return AccountName, different than if I used AccountID, but I need both calls to retrieve different sets of data from same table. I need first call to tblAccountInfo to get the CustomerID to use in the second call. :sigh:

[Michael Potter]

I have to agree with Marcus. The correlated subquery is redundant. I can't imagine any structure and/or data that would make this subquery useful. tb2.CustomerID = tb1.CustomerID will always return both columns from the same row. If CustomerID is duplicated within the table, then it will throw an error. It also seems strange that AccountName is not dependant upon AccountId as thier names imply. Are you sure your structure is designed correctly?

[AspDotNetDev]

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):

SELECT
tb1.SomeField,
(
SELECT TOP 1
tb2.AccountName
FROM tblAccountInfo AS tb2
WHERE
tb2.CustomerID = tb1.CustomerID
) AS strAccountName
FROM tblAccountInfo AS tb1
WHERE
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.

Thou mewling ill-breeding pignut!

[Aadhar Joshi]

First thing is u should avoid sub query as a column and make a inner join and take a column from sub table as it improves your performance when u fire your query with large data. Second is it works fine till sub query produces single record for parameter, if there is more than one row returned by sub query, it throws errors. Third for same u can use distinct clause in sub query.

[john john mackey]

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!