CODEWITHSUNDEEP
sqldatabase
Eneo
Eneo

Reputation:

SQL 2005 - The column was specified multiple times

I am getting the following error when trying to run this query in SQL 2005:

    SELECT tb.*
    FROM (
        SELECT *
        FROM vCodesWithPEs INNER JOIN vDeriveAvailabilityFromPE
        ON vCodesWithPEs.PROD_PERM = vDeriveAvailabilityFromPE.PEID
        INNER JOIN PE_PDP ON vCodesWithPEs.PROD_PERM = PE_PDP.PEID
    ) AS tb;

Error: The column 'PEID' was specified multiple times for 'tb'.

I am new to SQL.

Upvotes: 41

Views: 150192

Answers (5)

Rubeshkumar
Rubeshkumar

Reputation: 11

just give new alias name for the column that repeats,it worked for me.....

Upvotes: 1

MicSim
MicSim

Reputation: 26806

Looks like you have the column PEID in both tables: vDeriveAvailabilityFromPE and PE_PDP. The SELECT statement tries to select both, and gives an error about duplicate column name.

Upvotes: 6

Vinko Vrsalovic
Vinko Vrsalovic

Reputation: 340286

The problem, as mentioned, is that you are selecting PEID from two tables, the solution is to specify which PEID do you want, for example

 SELECT tb.*
    FROM (
        SELECT tb1.PEID,tb2.col1,tb2.col2,tb3.col3 --, and so on
        FROM vCodesWithPEs as tb1 INNER JOIN vDeriveAvailabilityFromPE as tb2
        ON tb1.PROD_PERM = tb2.PEID 
        INNER JOIN PE_PDP tb3 ON tb1.PROD_PERM = tb3.PEID
    ) AS tb;

That aside, as Chris Lively cleverly points out in a comment the outer SELECT is totally superfluous. The following is totally equivalent to the first.

        SELECT tb1.PEID,tb2.col1,tb2.col2,tb3.col3 --, and so on
        FROM vCodesWithPEs as tb1 INNER JOIN vDeriveAvailabilityFromPE as tb2
        ON tb1.PROD_PERM = tb2.PEID 
        INNER JOIN PE_PDP tb3 ON tb1.PROD_PERM = tb3.PEID

or even 

        SELECT * 
        FROM vCodesWithPEs as tb1 INNER JOIN vDeriveAvailabilityFromPE as tb2
        ON tb1.PROD_PERM = tb2.PEID 
        INNER JOIN PE_PDP tb3 ON tb1.PROD_PERM = tb3.PEID

but please avoid using SELECT * whenever possible. It may work while you are doing interactive queries to save typing, but in production code never use it.

Upvotes: 56

ChrisLively
ChrisLively

Reputation: 88072

Instead of using * to identify collecting all of the fields, rewrite your query to explicitly name the columns you want. That way there will be no confusion.

Upvotes: 2

Jeremy Smyth
Jeremy Smyth

Reputation: 23503

You're joining three tables, and looking at all columns in the output (*).

It looks like the tables have a common column name PEID, which you're going to have to alias as something else.

Solution: don't use * in the subquery, but explicitly select each column you wish to see, aliasing any column name that appears more than once.

Upvotes: 4

Related Questions