sql 2005 - The column has been specified multiple times

advertisements

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.


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.