SQL 2005 - The column was specified multiple times

Question

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.

Just out of curiosity, why do you have the outer "select tb.*"? — NotMe, Jun 29 '09 at 14:29

Vinko Vrsalovic · Accepted Answer · 2009-06-29T14:42:16.660

56

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.

edited Jun 29 '09 at 14:42

answered Jun 29 '09 at 14:29

Vinko Vrsalovic

330,807
53
334
373

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
3

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. – Kassi Sep 27 '20 at 11:01
this is proper solution and worked perfectly fine for me – BKM Apr 05 '21 at 06:08
Your answer's last query is same as the OP's query in question except for alias. How it works fine? – Shad Jun 16 '22 at 08:06
@Shad because the alias is exactly the problem, if you don't specify from what table the column you're selecting must come from, the server gets confused. I've always found that a bit silly especially when you are joining on that column. But that's the way things are. – Vinko Vrsalovic Jun 16 '22 at 15:39
I learned rather more than what I came here looking for. thank you sir. – Mateen Bagheri Jun 27 '22 at 11:55

MicSim · Answer 2 · 2016-04-20T10:59:50.353

6

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.

edited Apr 20 '16 at 10:59

answered Jun 29 '09 at 14:26

MicSim

26,265
16
90
133

He does have it in both tables, not only looks like :) – Vinko Vrsalovic Jun 29 '09 at 14:27
You're right, Vinko. :-) On a sidenote: Why my second table name doesn't get displayed in italics? (put it in **, but displays as *PE_PDP*) Preview is correct. – MicSim Jun 29 '09 at 14:32
I don't know, issue for uservoice I think. I tried some of the usual tricks (adding spaces or reformatting) but it didn't work – Vinko Vrsalovic Jun 29 '09 at 14:41
@MicSim I guess the italic formatting does not like underscore in the name PE_PDP – kristof Jun 29 '09 at 14:43

score 4 · Answer 3 · answered Jun 29 '09 at 14:28

4

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.

answered Jun 29 '09 at 14:28

Jeremy Smyth

23,270
2
52
65

Aliasing! That's what I needed to know! Thank you! – Callat Dec 21 '18 at 15:47

score 2 · Answer 4 · answered Jun 29 '09 at 14:29

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.

answered Jun 29 '09 at 14:29

NotMe

87,343
27
171
245

score 1 · Answer 5 · answered Dec 05 '16 at 13:40

1

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

answered Dec 05 '16 at 13:40

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

SQL 2005 - The column was specified multiple times

5 Answers5

Linked

Related