23

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.

Improve this question
3
  • is there an id column in the profiles table that is conflicting with another table's id column? You are selecting all columns, I would recommend reducing the number of columns selecting (change * to column names) and see if that fixes your problem. Commented Feb 13, 2012 at 13:02
  • Could it be that both the Profiles and the FriendList table in your inner SELECT have an ID column?? So you would basically have two columns, both called ID - and you're selecting everything from that JOIN - so which ID are you referring to when you say SELECT DISTINCT Profiles.ID ..... ?!?!? Commented Feb 13, 2012 at 13:03
  • marc_s & Robert - yes, that's what is going on. I guessed as much but this is just beyond my SQL understanding. The problem is that the SQL statement is MASSIVE and is built dynamically. I cut it to the bare bones for this question. I am going to toss it back to the person as the knock-on effects of messing with either this or the db are too awful to contemplate. Commented Feb 13, 2012 at 13:26

5 Answers 5

Reset to default
21

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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

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.

Improve this answer

1 Comment

yes. but is there a way to just rename one column? My table has hundreds of columns. I do not want to type each and every one's name.
0

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

Improve this answer

Comments

0

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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.