sql 2005 - The column was specified multiple times

Question

Hi,

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. Thank You in advance for your advise.

Eneo.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.