The column 'TRANSACTION_EVENT_ID' was specified multiple times for 'YY'

Question

I have written a query like this:

SELECT  
    YY.ACCOUNT_ID,
    YY.TRANSACTION_EVENT_ID 
FROM
   (SELECT * 
    FROM dbo.TRANSACTION TE
    JOIN dbo.FUND_TRANSACTION FT
    ON TE.TRANSACTION_EVENT_ID = FT.TRANSACTION_EVENT_ID
    JOIN ACCOUNT A
    ON FT.ACCOUNT_ID = A.ACCOUNT_ID) AS YY

and getting error like this:

The column 'TRANSACTION_EVENT_ID' was specified multiple times for 'YY'

Checked this : SQL The column 'Id' was specified multiple times

However, the issue is not resolved. What am I doing wrong?

avoid `SELECT * FROM` - specify the column names that you want to select. From your query you will get 2 TRANSACTION_EVENT_ID one from `dbo.TRANSACTION` and another from `dbo.FUND_TRANSACTION`. So there goes the confusion. — Abhishek, Nov 03 '15 at 12:48

score 5 · Answer 1 · answered Nov 03 '15 at 12:51

The issue is with the SELECT *. Since the same column names can be repeat in the selection.

Explicitly mention the column names. The below query will work.

SELECT  YY.ACCOUNT_ID, YY.TRANSACTION_EVENT_ID 
FROM (  SELECT FT.ACCOUNT_ID, FT.TRANSACTION_EVENT_ID
        FROM dbo.[TRANSACTION] TE
        JOIN dbo.FUND_TRANSACTION FT ON TE.TRANSACTION_EVENT_ID = FT.TRANSACTION_EVENT_ID
        JOIN ACCOUNT A ON FT.ACCOUNT_ID = A.ACCOUNT_ID
     ) AS YY

score 2 · Answer 2 · answered Nov 03 '15 at 12:51

You have to specify from which table TRANSACTION_EVENT_ID you want to select. You have to specify column list with aliases / table names instead of *. Something like:

SELECT  
    YY.ACCOUNT_ID,
    YY.TRANSACTION_EVENT_ID 
FROM
   (SELECT A.ACCOUNT_ID,
           TE.TRANSACTION_EVENT_ID  
    FROM dbo.TRANSACTION TE
    JOIN dbo.FUND_TRANSACTION FT
    ON TE.TRANSACTION_EVENT_ID = FT.TRANSACTION_EVENT_ID
    JOIN ACCOUNT A
    ON FT.ACCOUNT_ID = A.ACCOUNT_ID) AS YY

The column 'TRANSACTION_EVENT_ID' was specified multiple times for 'YY'

2 Answers2