SQL 2005 – The column was specified multiple times

by

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.

More Related Contents:

  1. SQL Data models
  2. MySQL query finding values in a comma separated string
  3. How to randomly select rows in SQL?
  4. What’s the difference between TRUNCATE and DELETE in SQL
  5. SQL best practice to deal with default sort order
  6. How can I get column names from a table in Oracle?
  7. Deleting duplicate rows from sqlite database
  8. How to replace a string in a SQL Server Table Column
  9. single fixed table with multiple columns vs flexible abstract tables
  10. How can I send email from PostgreSQL trigger?
  11. How to design a database for User Defined Fields?
  12. How to delete Duplicates in MySQL table
  13. Normalization in plain English
  14. Core Data Performance with Single Parent Entity
  15. Oracle – How to create a materialized view with FAST REFRESH and JOINS
  16. Why does VARCHAR need length specification?
  17. How to Optimize Queries in a Database – The Basics
  18. Know relationships between all the tables of database in SQL Server
  19. Bulk/batch update/upsert in PostgreSQL
  20. How to populate calendar table in Oracle?
  21. Create date from day, month, year fields in MySQL
  22. Grant privileges for a particular database in PostgreSQL
  23. How do I create and query linked database servers in SQL Server?
  24. The object ‘DF__*’ is dependent on column ‘*’ – Changing int to double
  25. What does ON [PRIMARY] mean?
  26. How do you interpret a query’s explain plan?
  27. Oracle DB: How can I write query ignoring case?
  28. SQL difference between rows
  29. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  30. SQL Server equivalent of WM_CONCAT function [duplicate]

Leave a Comment