Error column reference title is ambiguous - Исправление ошибок и поиск оптимальных решений проблем

I tried the following select:

SELECT (id,name) FROM v_groups vg 
inner join people2v_groups p2vg on vg.id = p2vg.v_group_id
where p2vg.people_id =0;

and I get the following error column reference id is ambiguous.

If I try the same SELECT, but I only ask for name and not for id also, it works.

Any suggestions?

TylerH

20.5k62 gold badges75 silver badges97 bronze badges

asked Mar 22, 2012 at 11:09

You need the table name/alias in the SELECT part (maybe (vg.id, name)) :

SELECT (vg.id, name) FROM v_groups vg 
inner join people2v_groups p2vg on vg.id = p2vg.v_group_id
where p2vg.people_id =0;

Yuri

4,0681 gold badge25 silver badges44 bronze badges

answered Mar 22, 2012 at 11:11

JScoobyCedJScoobyCed

9,9836 gold badges34 silver badges57 bronze badges

I suppose your p2vg table has also an id field , in that case , postgres cannot find if the id in the SELECT refers to vg or p2vg.

you should use SELECT(vg.id,vg.name) to remove ambiguity

answered Mar 22, 2012 at 11:12

dweevesdweeves

5,49521 silver badges28 bronze badges

SELECT (vg.id, name) FROM v_groups vg 
INNER JOIN people2v_groups p2vg ON vg.id = p2vg.v_group_id
WHERE p2vg.people_id = 0;

user unknown

35k11 gold badges73 silver badges121 bronze badges

answered Mar 22, 2012 at 11:15

JanakiJanaki

18512 bronze badges

SELECT vg.id, 
       vg.name
  FROM v_groups vg INNER JOIN  
       people2v_groups p2vg ON vg.id = p2vg.v_group_id
 WHERE p2vg.people_id = 0;

answered Sep 25, 2017 at 6:55

As a additional note: I got this error when I was using a CTE for a join resulting in an ambiguity. I was using the CTE in FROM with an alias and despite prefixing the SELECTed column with the CTE’s alias, postgres would still produce this error on the prefixed column call. It was a bit trickier to discover as my query was long.

Hope it helps someone out there.

answered May 24, 2022 at 10:06

RTDRTD

5856 silver badges19 bronze badges

Источник

Содержание

Solved: SQL ambiguous column name [100% Working]
What is an ambiguous column name error in SQL?
What is the cause of this error?
According to Oracle documents
ORA-00918 column ambiguously defined
Example of SQL ambiguous column name
Solve the “Ambiguous Column Name” Error in SQL
Summary
References
Read More

Solved: SQL ambiguous column name [100% Working]

Table of Contents

What is an ambiguous column name error in SQL?

SQL ambiguous column name is one most common error we are facing while performing a join query with two or more tables, this type of error occurs when we try to retrieve data from two or more tables using SQL join and more than one tables have the same column name appears in the selection.

What is the cause of this error?

The root cause of this error is the same column name in two or more tables and selecting the same column with the same name when performing a join

According to Oracle documents

ORA-00918 column ambiguously defined

Cause of error: When the same column name exists in more than one table in a join than one table and is thus referenced ambiguously.
Action: To overcome this error we need to prefix references to column names that exist in multiple tables with either the table name or a table alias and a period (.),

Example of SQL ambiguous column name

Create two tables for patient and doctor as follow

patient_table(patient_id,patient_name,doctor_id,city)

patient_id	patient_name	doctor_id	city
101	Rekha	11	Surat
102	Reema	12	Vapi
103	Jaya	13	Navasari

doctor_table(doctor_id,doctor_name,city)

doctor_id	doctor_name	city
11	Rahul	Surat
12	Prashant	Vapi
13	Asif	Navasari

Next create PATIENT Table

Next create DOCTOR Table

Example 1: Write SQL query to display all patient data with doctor id and doctor city name

In the above query, we used an inner join between two tables patient and doctor to retrieve data from both the tables
In the above query we specify city and doctor_id columns, both the columns are common in both the tables so we will get an ambiguous error for both the columns

OUTPUT:

Error Message :

Solve the “Ambiguous Column Name” Error in SQL

To solve the ambiguous column name error we need to prefixed column name by its table name when referenced with the SQL select statement, the column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN

Example 2 : Write SQL query to display all patient data with doctor id and doctor city name , also specify table name with column name in the SQL select statement

OUTPUT:

Summary

In this article on SQL ambiguous column names, we have covered what is ambiguous column name error in SQL, the cause of ambiguous column name error, how this error explained in oracle document ORA-00918 and also explained SSQLambiguous column name error with practical examples.

References

Related Keywords: ambiguous column name join, sql ambiguous column name, ambiguous column name, ambiguous column name sql, column reference is ambiguous, ms sql ambiguous column name, sql query ambiguous column name

Didn’t find what you were looking for? Perform a quick search across GoLinuxCloud

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

For any other feedbacks or questions you can either use the comments section or contact me form.

Thank You for your support!!

to stay connected and get the latest updates

Источник

I am using PostgreSQL as my database. And I need to create an entry in the database, and if it’s already exists, just update its fields, but one of the fields should be updated only if it’s not set.

I’ve used info from this question: https://stackoverflow.com/questions/13305878/dont-update-column-if-update-value-is-null, it’s quite related to what I have.

I tried to use this query, but when I run it, it errors with Column reference 'affiliate_code' is ambiguous:

INSERT INTO accounts (id, token, affiliate_code)
VALUES (value1, value2, value3)
ON CONFLICT (id) DO
UPDATE SET token = value2,
  affiliate_code = COALESCE(affiliate_code, value3);

(the real values are substituted, of course).

If I replace affiliate_code = COALESCE(affiliate_code, value3) with affiliate_code = value3, everything works, but not in the way I want it to work.

How can I make this work?

Here is how my table is defined:

CREATE TABLE accounts (
  id VARCHAR NOT NULL UNIQUE,
  token VARCHAR NOT NULL,
  affiliate_code VARCHAR
);

Источник

What is an ambiguous column name error in SQL?

What is the cause of this error?

The root cause of this error is the same column name in two or more tables and selecting the same column with the same name when performing a join

According to Oracle documents

ORA-00918 column ambiguously defined

Cause of error: When the same column name exists in more than one table in a join than one table and is thus referenced ambiguously.
Action: To overcome this error we need to prefix references to column names that exist in multiple tables with either the table name or a table alias and a period (.),

ALSO READ: SQL SUBSTRING Function Explained [Beginners]

Example of SQL ambiguous column name

Create two tables for patient and doctor as follow

patient_table(patient_id,patient_name,doctor_id,city)

patient_id	patient_name	doctor_id	city
101	Rekha	11	Surat
102	Reema	12	Vapi
103	Jaya	13	Navasari

doctor_table(doctor_id,doctor_name,city)

doctor_id	doctor_name	city
11	Rahul	Surat
12	Prashant	Vapi
13	Asif	Navasari

Next create PATIENT Table

Create table patient_table
(
   patient_id int primary key, 
   patient_name varchar(20),
   doctor_id int,
   city varchar(20)
)

Next create DOCTOR Table

Create table doctor_table
(
   doctor_id int primary key, 
   doctor_name varchar(20),
   city varchar(20)
)

Example 1: Write SQL query to display all patient data with doctor id and doctor city name

select patient_id as 'Patient ID' ,patient_name as 'Patient Name',doctor_id as 'Doctor ID',city as 'Doctor City' from patient_table , doctor_table where patient_table.doctor_id=doctor_table.doctor_id

In the above query, we used an inner join between two tables patient and doctor to retrieve data from both the tables
In the above query we specify city and doctor_id columns, both the columns are common in both the tables so we will get an ambiguous error for both the columns

OUTPUT:

Error Message :

Msg 209,Level 16,State 1,Line 1
Ambiguous column name 'doctor_id'
Msg 209,Level 16,State 1,Line 1
Ambiguous column name 'city'

Solve the “Ambiguous Column Name” Error in SQL

ALSO READ: 35+ SQL Functions Explained in Detail [Practical Examples]

Example 2 : Write SQL query to display all patient data with doctor id and doctor city name , also specify table name with column name in the SQL select statement

select patient_table.patient_id as 'Patient ID' ,patient_table.patient_name as 'Patient Name',doctor_table.doctor_id as 'Doctor ID',doctor_table.city as 'Doctor City' from patient_table , doctor_table where patient_table.doctor_id=doctor_table.doctor_id

OUTPUT:

Summary

References

SQL joins

SQL Ambiguous Column Name

Didn’t find what you were looking for? Perform a quick search across GoLinuxCloud

Источник

Здравствуйте уважаемые коллеги! Предыстория: есть некоторые свои наработки на php — подобие фреймфорка, в нем есть некоторый код который динамически формирует строку SQL-запроса
наподобие такой:

$query="select main_user as TMF from main_users 
left join main_users_grps_view on main_users_grps_view.main_user_id=main_users.main_user_id 
left join main_users_auth_view on main_users_auth_view.main_user_id=main_users.main_user_id 
left join main_grps on main_grps.main_grp_id = main_users_grps_view.main_grp_id 
left join main_account_types on main_account_types.main_account_type_id=main_users.main_account_type_id 
where main_users.main_user_id=1294

данный запрос выдает ошибку «ERROR: column reference «main_user» is ambiguous» поскольку колонка main_users повторяется в нескольких включенных таблицах/представлениях. Структура таблиц/представлений такова, что в принципе пофик из какой таблицы брать поле main_user, можно ли как то сказать postgresql об этом? чтоб он не выдавал эту ошибку, в приведенном выше запросе, а брал эту колонку из первой попавшейся ему таблицы?
Если сделать main_user=main_users_grps_view.main_user то естественно все работает, но делать это на стороне PHP в моем случае не очень удобно, возможно на стороне postgres это получится более элегантно. Заранее спасибо за помощь

Источник

Dung Do Tien Oct 01 2021 656

I have a small project with Asp.Net Core 3.1 and using PostgreSQL database. Now I want to get all ticket of a member so I created a function database as below:

 CREATE OR REPLACE FUNCTION public.func_ticket_getbymemberid (
  _memberid integer,
  _fromdate timestamp,
  _todate timestamp
)
RETURNS TABLE (
  id integer,
  memberid integer,
  createddate timestamp,
  score integer
) AS
$body$
BEGIN
    RETURN QUERY
    SELECT id, memberid, createddate, score
    FROM racinggameticket
    WHERE memberid = _memberid AND createddate between _fromdate AND _todate;
END
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;

And when I try to execute in code c# by using Dapper I got an exception throw 42702: column reference «id» is ambiguous.

 42702: column reference "id" is ambiguous
at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Threading.Tasks.ValueTask`1.get_Result()
at Npgsql.NpgsqlDataReader.<NextResult>d__46.MoveNext()
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.NpgsqlDataReader.NextResult()
at Npgsql.NpgsqlCommand.<ExecuteDbDataReader>d__100.MoveNext()
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at Npgsql.NpgsqlCommand.ExecuteReader()
at Project.Data.RacingGameTicket.RacingGameDal.GetTicketByMember(Int32 memberId, DateTime fromDate, DateTime toDate) in E:ProjectPhilipMainSource_NewAdminSourcegamefrontendAutoCoreProject.DataRacingGameTicketRacingGameDal.cs:line 53
at Project.BLL.RacingGameTicket.RacingGameBLL.GetTicketByMember(Int32 memberId, DateTime fromDate, DateTime toDate) in E:ProjectPhilipMainSource_NewAdminSourcegamefrontendAutoCoreProject.BLLRacingGameTicketRacingGameBLL.cs:line 27
at AutoPhilippine.Controllers.RacingGameController.GetTicketByMember() in E:ProjectPhilipMainSource_NewAdminSourcegamefrontendAutoPhilippineAutoPhilippineControllersRacingGameController.cs:line 89

This is my racinggameticket table

  --- racinggameticket
     -- id integer
     -- memberid integer
     -- createddate timestamp
     -- score integer

My select command is very simple and the table does not have two id columns. So I really not understand why them ambiguous.

Thank you for any suggestions.

Have 2 answer(s) found.

Diego Ganchozo
Oct 01 2021
In the PostgreSQL database, you have to define an alias for the table and using that alias to determine the columns of the table. You can change your database function as below:
```
 SELECT r.id, r.memberid, r.createddate, r.score
    FROM racinggameticket r
    WHERE r.memberid = _memberid AND r.createddate between _fromdate AND _todate;
```
I hope this answer is all you need.
Select command in Postgre database is not same with Mssql server database. In Mssql you do not need to define an alias for a table but Postgres is required. So you can add more alias as below:
```
 SELECT game.id, game.memberid, game.createddate, game.score
    FROM racinggameticket game
    WHERE game.memberid = _memberid AND game.createddate between _fromdate AND _todate;
```
And it worked for you.

Note: This issue is not related to Asp.net Core or Dapper.

Related Q&A May You Like

The following sections have been defined but have not been rendered by the page
Fix error: failed to push some refs to repository in Git
Fatal: unable to access: SSL certificate problem: certificate has expired
Could not load file or assembly System.Net.Http, Version=4.0.0.0 in .Net
Error response from daemon unauthorized: HTTP Basic: Access denied CICD GitLab
Error: The json property name for collides with another property in .Net 6
Error: Sequence contains no matching element in C# Asp.Net
Data is Null. This method or property cannot be called on Null values in Asp.Net and MySql
Error: SSL Connection error in MySQL using C# Asp.Net 6
Unable to make the session state request to the session state server in .Net
Github error: src refspec master does not match any
Unable to create an object of type ‘DbContext’ in EF Core
GitHub remote: Support for password authentication was removed on August 13, 2021
Error remote: Repository not found on GitHub when clone
The SDK ‘Microsoft.NET.Sdk.Web’ specified could not be found

Leave An Answer

* NOTE: You need Login before leave an answer

* Type maximum 2000 characters.

* All comments have to wait approved before display.

* Please polite comment and respect questions and answers of others.

Источник

Published: 11 Apr 2015
Last Modified Date: 24 Aug 2022

Issue

When trying to refresh a data extract containing custom SQL, the following error might occur:

ERROR: column reference «<field name>» is ambiguous; Error while executing the query

Environment

Tableau Desktop
Tableau Data Extract
Custom SQL

Resolution

Verify that the SQL Query completes successfully when run directly against the database. If the query does not complete successfully, work with your database administrator to address issues in the query.

Cause

The error occurs within the data source and is not related to Tableau.

Additional Information

More information on the «Column reference «<name>» is ambiguous error can be found below:

Identifier Qualifiers at MySQL
8 Tips Absolute Beginners can Use to Fix SQL Queries at Vertabelo Academy

Источник

Solved: SQL ambiguous column name [100% Working]

What is an ambiguous column name error in SQL?

What is the cause of this error?

According to Oracle documents

ORA-00918 column ambiguously defined

Example of SQL ambiguous column name

Solve the “Ambiguous Column Name” Error in SQL

Summary

References

Read More

What is an ambiguous column name error in SQL?

What is the cause of this error?

According to Oracle documents

ORA-00918 column ambiguously defined

Example of SQL ambiguous column name

Solve the “Ambiguous Column Name” Error in SQL

Summary

References

Read More

Related Q&A May You Like

Issue

Environment

Resolution

Cause

Additional Information

Читайте также: