SQL Duplicate Column Name Error

SQL Duplicate column name error

Your Profile and FriendList tables both have an ID column. Because you say select *, you're getting two columns named ID in the sub-select which is aliased to Profiles, and SQL doesn't know which one Profiles.ID refers to (note that Profiles here is referring to the alias of the sub-query, not the table of the same name).

Since you only need the ID column, you can change it to this:

SELECT DISTINCT Profiles.ID FROM 
( select Profiles.ID from Profiles RIGHT JOIN FriendList ON (FriendList.Profile = 15237) 
order by LastLoggedIn DESC ) as Profiles

Why do I get a duplicate column name error only when I SELECT FROM (SELECT)

Your first query returns four columns:

• tag_id
• article_id
• tag_id
• tag

Duplicate column names are allowed in a result set, but are not allowed in a table -- or derived table, view, CTE, or most subqueries (an exception are EXISTS subqueries).

I hope you can see the duplicate. There is no need to select tag_id twice, because the JOIN requires that the values are the same. So just select three columns:

SELECT tr.tag_id, tr.article_id, t.tag
FROM _dia_tagsrel tr JOIN
     _dia_tags t
     ON tr.tag_id = t.tag_id

Duplicate column name in SQL

You get the error because both tables in your query has a column named product_name and selected column names must be unique.

You make your aliases like this:

CREATE VIEW product_view AS SELECT p.product_name, si.product_name StoreItemsProductName -- continue with what you want to select...
FROM products p JOIN storeitems si
on p.product_id=si.product_id;

SQL Developer duplicate column name while creating view

Just need to add column aliases.

create view vw_sales_may as 
select customers.id as CustomerID, 
orderdetails.id as OrderDetailsID, 
sales.sale_date, 
sales.total_value 
from customers 
inner join sales on customers.id = sales.customer_id 
join orderdetails on orderdetails.customer_id = customers.id 
where to_char(sale_date,'MM') = 05;

SQL Duplicate column names not supported

Qualify all column references and you will minimize such problems.

with firstPay as (
       select lr.loanId, min(lr.dueday) as dueday
       from loansRepaid lr
       group by lr.loanId
      )
select lp.*
from firstPay fp join
     loansRepaid lr
     on fp.loanId = lr.loanId and
        fp.dueDate = lr.dueDate;

In this case, the solution is that you only need the rows from lp in the outer query. The columns from fp are just duplicating those.

You may find that performance is better with a correlated subquery:

select lr.*
from loansrepaid lr
where lr.dueday = (select min(lr2.dueday)
                   from loansrepaid lr2
                   where lr2.loanid = lr.loanid
                  );

In particular, this can take advantage of an index on loansrepaid(loanid, dueday).

---

Related Topics