SQL CORRELATION

Question

I have this query:

SELECT COUNT (DISTINCT CUSTOMER_ACCOUNT.ID) AS  NUMBER_OF_ACCOUNTS 
FROM CUSTOMER_ACCOUNT 
INNER JOIN ACCOUNT ON CUSTOMER_ACCOUNT.ACCOUNT_ID=ACCOUNT.ID 
INNER JOIN CUSTOMER_ACCOUNT ON LOAD_ACCOUNT_DETAIL_0.ID = CUSTOMER_ACCOUNT.ID
WHERE Convert(datetime, convert(char(10), [CUSTOMER_ACCOUNT].CREATED_ON, 101)) BETWEEN '2009-01-01' AND '2009-05-1'
  AND CUSTOMER_ACCOUNT.DELETED!='1' 
  AND ACCOUNT.DELETED !='1'and  LOAD_ACCOUNT_DETAIL_0.ACCOUNT_STATUS='1'

I get this error:

Msg 1013, Level 16, State 1, Line 1 The objects "CUSTOMER_ACCOUNT" and "CUSTOMER_ACCOUNT" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

Not sure how to solve - any ideas?

Answer 1

You have the CUSTOMER_ACCOUNT self joining. You need to alias at least one of them.

SELECT COUNT (DISTINCT CUSTOMER_ACCOUNT.ID) AS  NUMBER_OF_ACCOUNTS 
FROM CUSTOMER_ACCOUNT CA1 INNER JOIN
    ACCOUNT ON CA1.ACCOUNT_ID=ACCOUNT.ID INNER JOIN
    CUSTOMER_ACCOUNT CA2 ON LOAD_ACCOUNT_DETAIL_0.ID = CA2.CUSTOMER_ACCOUNT.ID
WHERE
Convert(datetime,convert(char(10),[CA1].CREATED_ON,101)) 
BETWEEN '2009-01-01' AND '2009-05-1'  AND  CA1.DELETED!='1' AND 
ACCOUNT.DELETED !='1'and  LOAD_ACCOUNT_DETAIL_0.ACCOUNT_STATUS='1'

Answer 2

you are specifying a table twice in your query 'CUSTOMER ACCOUNT' is used as the FROM as well as in an INNER JOIN.

If you change your query with aliases for the CUSTOMER_ACCOUNT tables you should be okay.

example:

FROM CUSTOMER_ACCOUNT AS cust_acct

INNER JOIN CUSTOMER_ACCOUNT as cust_acct_join

then when you reference the columns, be sure to use the alias you created

example:

SELECT COUNT (DISTINCT **cust_acct.**CUSTOMER_ACCOUNT.ID) AS  NUMBER_OF_ACCOUNTS

Answer 3

I think you just made a mistake in your SQL. This line:

INNER JOIN CUSTOMER_ACCOUNT ON LOAD_ACCOUNT_DETAIL_0.ID = CUSTOMER_ACCOUNT.ID

Should probably be this?

INNER JOIN LOAD_ACCOUNT_DETAIL_0 ON LOAD_ACCOUNT_DETAIL_0.ID = CUSTOMER_ACCOUNT.ID

Unless your intention was actually to do a self-join, in which case you would need to alias the table names (as others have mentioned)

Answer 4

If you self-join a table you have to use aliases:

SELECT … FROM CUSTOMER_ACCOUNT AS C1 … CUSTOMER_ACCOUNT AS C2 …

Answer 5

Do something like:

SELECT COUNT (DISTINCT t1.ID) AS  NUMBER_OF_ACCOUNTS 
FROM CUSTOMER_ACCOUNT t1 INNER JOIN
ACCOUNT ON t1.ACCOUNT_ID=ACCOUNT.ID INNER JOIN
CUSTOMER_ACCOUNT t2 ON LOAD_ACCOUNT_DETAIL_0.ID = t2.ID
WHERE Convert(datetime,convert(char(10),t1.CREATED_ON,101)) 
BETWEEN '2009-01-01' AND '2009-05-1'  AND  t1.DELETED!='1' AND 
ACCOUNT.DELETED !='1' and  LOAD_ACCOUNT_DETAIL_0.ACCOUNT_STATUS='1'