Menu
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.
456
This typically happens when you retrieve data from multiple tables with the same column name using a JOIN statement. You might receive an error like this: ERROR: Column 'col_name' in field list is ambiguous. To avoid this error, you should define the table when inserting the column name in the SQL statement.
Duplicate names within a single CTE definition aren't allowed. The number of column names specified must match the number of columns in the result set of the CTE_query_definition.
It records inaccurate data and is also unable to fetch the correct data from the database. To remove the duplicate columns we use the DISTINCT operator in the SELECT statement as follows: Syntax: SELECT DISTINCT column1, column2, ...
A column name was specified twice in a CREATE or INSERT statement. Column names must be unique within a table, view, or cluster. In a CREATE statement, change one of the column names to a new, unique column name. In an INSERT statement, remove one of the duplicate names.
Column names must be unique within a table, view, or cluster. In a CREATE statement, change one of the column names to a new, unique column name. In an INSERT statement, remove one of the duplicate names. 1 rows inserted.
One of the simplest ways to solve an “ambiguous name column” error — without changing column name — is to give the tables you want to join an alias. This sends a clear information to the SQL Machine the columns are different.
This means two columns have the same column name — that is the “Name” column. The SQL Machine is confused as to which “Name” out of the two tables you are referring to. It is ambiguous — not clear. To clarify this, add the alias of either or both TABLE1 or TABLE2 to the columns having the same name.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
