38

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.

D'Arcy Rittich
  • 160,735
  • 37
  • 279
  • 278

5 Answers5

54

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.

Vinko Vrsalovic
  • 253,260
  • 52
  • 326
  • 367
  • 1
    Using select * is fine when you need all columns - even in production. It should be avoided when using joins or complex queries, not avoided **at all costs** regardless how simplistic the query is. – Steve Bauman Sep 06 '19 at 17:20
  • 2
    The problem with `SELECT *` is that tables evolve over time, and you might start bringing in lots more data than you expected, without realizing it. Also, you win nothing by using `SELECT *` instead of making the fields you are interested in explicit (except a few characters of typing.) – Vinko Vrsalovic Sep 09 '19 at 10:50
  • That's true, fair enough! :) – Steve Bauman Sep 09 '19 at 12:59
  • 1
    I agree, that `select *` should be avoided. Really nice advice. – hesed Sep 27 '20 at 11:01
  • this is proper solution and worked perfectly fine for me – BKM Apr 05 '21 at 06:08
4

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.

MicSim
  • 25,746
  • 15
  • 88
  • 129
3

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.

Jeremy Smyth
  • 22,724
  • 2
  • 50
  • 65
2

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.

NotMe
  • 86,296
  • 27
  • 170
  • 243
1

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

Rubeshkumar
  • 11
  • 1
  • 3
    Please consider editing your answer to give more detail as to why your solution works. This will help OP and anyone that has the same problem in future – CallumDA Dec 05 '16 at 14:05