CODEWITHSUNDEEP
sql
Steve
Steve

Reputation: 1402

SQL Duplicate column name error

I am trying to find an error in a massive SQL statement (not mine) - I have cut a lot of it out to make it readable - even pared down it still throws the error

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

This returns an error

Duplicate column name 'ID'

I have tested the the last part (select * from Profiles ... order by LastLoggedIn DESC) and it works fine by itself

I have tried to troubleshoot by changing column names in the DISTINCT section without any luck.

One solution I read was to remove the DISTINCT, but that didn't help.

I just can't see where the duplicate column error can be coming from. Could it be a database integrity problem?

Any help much appreciated.

Upvotes: 23

Views: 170736

Answers (5)

Tundey
Tundey

Reputation: 2965

Replace the "select *" with "select col1, col2..." and the error should become apparent (i.e. multiple columns named "ID"). Nothing to do with distinct or database integrity.

Upvotes: 3

Ned Batchelder
Ned Batchelder

Reputation: 375584

Profiles and FriendList both have an ID column. You are asking to call the entire join "Profiles", and then using Profiles.ID, but SQL doesn't know which ID you mean.

Upvotes: 0

user554546
user554546

Reputation:

As the error says, each of the tables that you're joining together has a column named ID. You'll have to specify which ID column you want (Profiles.ID or FriendList.ID) or include ID in the join conditions.

Upvotes: 0

peroija
peroija

Reputation: 1982

you have a table called Profiles and you are "creating" a temp table called Profiles in your From, that would be my guess as to what is causing the problem. call your temp bananas and try SELECT DISTINCT bananas.ID FROM and see if that works

Upvotes: 0

Blorgbeard
Blorgbeard

Reputation: 103467

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

Upvotes: 21

Related Questions