Ambiguity with column reference

up vote
2
down vote

favorite

I try to run a simple code as follows:

Create Table weather (
 city varchar(80),
 temp_lo int,
 temp_hi int,
 prcp real,
 date date
);
Insert Into weather Values ('A', -5, 40, 25, '2018-01-10');
Insert Into weather Values ('B', 5, 45, 15, '2018-02-10');

Create Table cities (
 city varchar(80),
 location point
);
Insert Into cities Values ('A', '(12,10)');
Insert Into cities Values ('B', '(6,4)');
Insert Into cities Values ('C', '(18,13)');

Select * From cities, weather Where city = 'A'

But what I get is

ERROR: column reference "city" is ambiguous.

What is wrong with my code?

asked Aug 8 at 13:16

Zakhar

93111

migrated from dba.stackexchange.com Aug 8 at 20:03

This question came from our site for database professionals who wish to improve their database skills and learn from others in the community.

add a commentÂ |Â

up vote
2
down vote

favorite

I try to run a simple code as follows:

Create Table weather (
 city varchar(80),
 temp_lo int,
 temp_hi int,
 prcp real,
 date date
);
Insert Into weather Values ('A', -5, 40, 25, '2018-01-10');
Insert Into weather Values ('B', 5, 45, 15, '2018-02-10');

Create Table cities (
 city varchar(80),
 location point
);
Insert Into cities Values ('A', '(12,10)');
Insert Into cities Values ('B', '(6,4)');
Insert Into cities Values ('C', '(18,13)');

Select * From cities, weather Where city = 'A'

But what I get is

ERROR: column reference "city" is ambiguous.

What is wrong with my code?

asked Aug 8 at 13:16

Zakhar

93111

migrated from dba.stackexchange.com Aug 8 at 20:03

This question came from our site for database professionals who wish to improve their database skills and learn from others in the community.

add a commentÂ |Â

up vote
2
down vote

favorite

I try to run a simple code as follows:

Create Table weather (
 city varchar(80),
 temp_lo int,
 temp_hi int,
 prcp real,
 date date
);
Insert Into weather Values ('A', -5, 40, 25, '2018-01-10');
Insert Into weather Values ('B', 5, 45, 15, '2018-02-10');

Create Table cities (
 city varchar(80),
 location point
);
Insert Into cities Values ('A', '(12,10)');
Insert Into cities Values ('B', '(6,4)');
Insert Into cities Values ('C', '(18,13)');

Select * From cities, weather Where city = 'A'

But what I get is

ERROR: column reference "city" is ambiguous.

What is wrong with my code?

asked Aug 8 at 13:16

Zakhar

93111

I try to run a simple code as follows:

Create Table weather (
 city varchar(80),
 temp_lo int,
 temp_hi int,
 prcp real,
 date date
);
Insert Into weather Values ('A', -5, 40, 25, '2018-01-10');
Insert Into weather Values ('B', 5, 45, 15, '2018-02-10');

Create Table cities (
 city varchar(80),
 location point
);
Insert Into cities Values ('A', '(12,10)');
Insert Into cities Values ('B', '(6,4)');
Insert Into cities Values ('C', '(18,13)');

Select * From cities, weather Where city = 'A'

But what I get is

ERROR: column reference "city" is ambiguous.

What is wrong with my code?

asked Aug 8 at 13:16

Zakhar

93111

asked Aug 8 at 13:16

Zakhar

93111

asked Aug 8 at 13:16

Zakhar

93111

asked Aug 8 at 13:16

Zakhar

93111

migrated from dba.stackexchange.com Aug 8 at 20:03

This question came from our site for database professionals who wish to improve their database skills and learn from others in the community.

migrated from dba.stackexchange.com Aug 8 at 20:03

This question came from our site for database professionals who wish to improve their database skills and learn from others in the community.

add a commentÂ |Â

2 Answers
2

active

oldest

votes

up vote
5
down vote

accepted

If I were you I'd model things slightly differently.

To normalise things a little, we'll start with the cities table and make a few changes:

create table cities (
 city_id integer primary key,
 city_name varchar(100), 
 location point
);

Note that I've used an integer to denote the ID and Primary Key of the table, and stored the name of the city separately. This gives you a nice easy to maintain lookup table. By using an integer as the primary key, we'll also use less space in the weather table when we're storing data.

Create Table weather (
 city_id integer,
 temp_lo int,
 temp_hi int,
 prcp real,
 record_date date
);

Note that I'm storing the id of the city rather than the name. Also, I've renamed date as it's not a good idea to name columns after SQL reserved words.

Ensure that we use IDs in the test data:

Insert Into weather Values (1, -5, 40, 25, '2018-01-10');
Insert Into weather Values (2, 5, 45, 15, '2018-02-10');

Insert Into cities Values (1,'A', '(12,10)');
Insert Into cities Values (2,'B', '(6,4)');
Insert Into cities Values (3,'C', '(18,13)');

Your old query:

Select * From cities, weather Where city = 'A'

The name was ambiguous because both tables have a city column, and the database engine doesn't know which city you mean (it doesn't automatically know if it needs to use cities.city or weather.city). The query also performs a cartesian product, as you have not joined the tables together.

Using the changes I have made above, you'd require something like:

Select * 
From cities, weather 
Where cities.city_id = weather.city_id
and city_name = 'A';

or, using newer join syntax:

Select * 
From cities
join weather on cities.city_id = weather.city_id
Where city_name = 'A';

The two queries are functionally equivalent - these days most people prefer the 2nd query, as it can prevent mistakes (eg: forgetting to actually join in the where clause).

answered Aug 8 at 13:39

Phil

2,0661417

add a commentÂ |Â

up vote
5
down vote

Both tables cities and weather have a column called city. On your WHERE clause you filter city = 'A', which table's city is it refering to?

You can tell the engine which one you want to filter by preceding the column with it's table name:

Select * From cities, weather Where cities.city = 'A'

You can also refer to tables with alias:

Select * 
From cities AS C, weather AS W 
Where C.city = 'A'

But most important, make sure that you join tables together, unless you want all records from both tables to be matched without criterion (cartesian product). You can join them with explicit INNER JOIN:

Select 
 * 
From 
 cities AS C
 INNER JOIN weather AS W ON C.city = W.city
Where 
 C.city = 'A'

In the example you mention, this query is used:

SELECT *
FROM weather, cities
WHERE city = name;

But in here, cities table has name column (instead of city which is the one you used). So this WHERE clause is linking weather and cities table together, since city is weather column and name is cities column and there is no ambiguity because both columns are named different.

answered Aug 8 at 13:23

EzLo

6,46561326

@EzLo I'm sorry you're proposed answer doesn't give correct results: "A" "(12,10)" "A" -5 40 "25" "2018-01-10" "A" "(12,10)" "B" 5 45 "15" "2018-02-10"
â€“Â Zakhar
Aug 8 at 14:16

1

@Zakhar City B is being listed because you replaced name which is a column with A which is a value, thus losing the join between both tables.
â€“Â EzLo
Aug 8 at 14:19

add a commentÂ |Â

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f51754758%2fambiguity-with-column-reference%23new-answer', 'question_page');

);

Post as a guest

Name

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

up vote
5
down vote

accepted

If I were you I'd model things slightly differently.

To normalise things a little, we'll start with the cities table and make a few changes:

create table cities (
 city_id integer primary key,
 city_name varchar(100), 
 location point
);

Create Table weather (
 city_id integer,
 temp_lo int,
 temp_hi int,
 prcp real,
 record_date date
);

Note that I'm storing the id of the city rather than the name. Also, I've renamed date as it's not a good idea to name columns after SQL reserved words.

Ensure that we use IDs in the test data:

Insert Into weather Values (1, -5, 40, 25, '2018-01-10');
Insert Into weather Values (2, 5, 45, 15, '2018-02-10');

Insert Into cities Values (1,'A', '(12,10)');
Insert Into cities Values (2,'B', '(6,4)');
Insert Into cities Values (3,'C', '(18,13)');

Your old query:

Select * From cities, weather Where city = 'A'

Using the changes I have made above, you'd require something like:

Select * 
From cities, weather 
Where cities.city_id = weather.city_id
and city_name = 'A';

or, using newer join syntax:

Select * 
From cities
join weather on cities.city_id = weather.city_id
Where city_name = 'A';

The two queries are functionally equivalent - these days most people prefer the 2nd query, as it can prevent mistakes (eg: forgetting to actually join in the where clause).

answered Aug 8 at 13:39

Phil

2,0661417

add a commentÂ |Â

up vote
5
down vote

accepted

If I were you I'd model things slightly differently.

To normalise things a little, we'll start with the cities table and make a few changes:

create table cities (
 city_id integer primary key,
 city_name varchar(100), 
 location point
);

Create Table weather (
 city_id integer,
 temp_lo int,
 temp_hi int,
 prcp real,
 record_date date
);

Note that I'm storing the id of the city rather than the name. Also, I've renamed date as it's not a good idea to name columns after SQL reserved words.

Ensure that we use IDs in the test data:

Insert Into weather Values (1, -5, 40, 25, '2018-01-10');
Insert Into weather Values (2, 5, 45, 15, '2018-02-10');

Insert Into cities Values (1,'A', '(12,10)');
Insert Into cities Values (2,'B', '(6,4)');
Insert Into cities Values (3,'C', '(18,13)');

Your old query:

Select * From cities, weather Where city = 'A'

Using the changes I have made above, you'd require something like:

Select * 
From cities, weather 
Where cities.city_id = weather.city_id
and city_name = 'A';

or, using newer join syntax:

Select * 
From cities
join weather on cities.city_id = weather.city_id
Where city_name = 'A';

The two queries are functionally equivalent - these days most people prefer the 2nd query, as it can prevent mistakes (eg: forgetting to actually join in the where clause).

answered Aug 8 at 13:39

Phil

2,0661417

add a commentÂ |Â

up vote
5
down vote

accepted

If I were you I'd model things slightly differently.

To normalise things a little, we'll start with the cities table and make a few changes:

create table cities (
 city_id integer primary key,
 city_name varchar(100), 
 location point
);

Create Table weather (
 city_id integer,
 temp_lo int,
 temp_hi int,
 prcp real,
 record_date date
);

Note that I'm storing the id of the city rather than the name. Also, I've renamed date as it's not a good idea to name columns after SQL reserved words.

Ensure that we use IDs in the test data:

Insert Into weather Values (1, -5, 40, 25, '2018-01-10');
Insert Into weather Values (2, 5, 45, 15, '2018-02-10');

Insert Into cities Values (1,'A', '(12,10)');
Insert Into cities Values (2,'B', '(6,4)');
Insert Into cities Values (3,'C', '(18,13)');

Your old query:

Select * From cities, weather Where city = 'A'

Using the changes I have made above, you'd require something like:

Select * 
From cities, weather 
Where cities.city_id = weather.city_id
and city_name = 'A';

or, using newer join syntax:

Select * 
From cities
join weather on cities.city_id = weather.city_id
Where city_name = 'A';

The two queries are functionally equivalent - these days most people prefer the 2nd query, as it can prevent mistakes (eg: forgetting to actually join in the where clause).

answered Aug 8 at 13:39

Phil

2,0661417

If I were you I'd model things slightly differently.

To normalise things a little, we'll start with the cities table and make a few changes:

create table cities (
 city_id integer primary key,
 city_name varchar(100), 
 location point
);

Create Table weather (
 city_id integer,
 temp_lo int,
 temp_hi int,
 prcp real,
 record_date date
);

Note that I'm storing the id of the city rather than the name. Also, I've renamed date as it's not a good idea to name columns after SQL reserved words.

Ensure that we use IDs in the test data:

Insert Into weather Values (1, -5, 40, 25, '2018-01-10');
Insert Into weather Values (2, 5, 45, 15, '2018-02-10');

Insert Into cities Values (1,'A', '(12,10)');
Insert Into cities Values (2,'B', '(6,4)');
Insert Into cities Values (3,'C', '(18,13)');

Your old query:

Select * From cities, weather Where city = 'A'

Using the changes I have made above, you'd require something like:

Select * 
From cities, weather 
Where cities.city_id = weather.city_id
and city_name = 'A';

or, using newer join syntax:

Select * 
From cities
join weather on cities.city_id = weather.city_id
Where city_name = 'A';

The two queries are functionally equivalent - these days most people prefer the 2nd query, as it can prevent mistakes (eg: forgetting to actually join in the where clause).

answered Aug 8 at 13:39

Phil

2,0661417

answered Aug 8 at 13:39

Phil

2,0661417

answered Aug 8 at 13:39

Phil

2,0661417

answered Aug 8 at 13:39

Phil

2,0661417

add a commentÂ |Â

up vote
5
down vote

Both tables cities and weather have a column called city. On your WHERE clause you filter city = 'A', which table's city is it refering to?

You can tell the engine which one you want to filter by preceding the column with it's table name:

Select * From cities, weather Where cities.city = 'A'

You can also refer to tables with alias:

Select * 
From cities AS C, weather AS W 
Where C.city = 'A'

Select 
 * 
From 
 cities AS C
 INNER JOIN weather AS W ON C.city = W.city
Where 
 C.city = 'A'

In the example you mention, this query is used:

SELECT *
FROM weather, cities
WHERE city = name;

answered Aug 8 at 13:23

EzLo

6,46561326

@EzLo I'm sorry you're proposed answer doesn't give correct results: "A" "(12,10)" "A" -5 40 "25" "2018-01-10" "A" "(12,10)" "B" 5 45 "15" "2018-02-10"
â€“Â Zakhar
Aug 8 at 14:16

1

@Zakhar City B is being listed because you replaced name which is a column with A which is a value, thus losing the join between both tables.
â€“Â EzLo
Aug 8 at 14:19

add a commentÂ |Â

up vote
5
down vote

Both tables cities and weather have a column called city. On your WHERE clause you filter city = 'A', which table's city is it refering to?

You can tell the engine which one you want to filter by preceding the column with it's table name:

Select * From cities, weather Where cities.city = 'A'

You can also refer to tables with alias:

Select * 
From cities AS C, weather AS W 
Where C.city = 'A'

Select 
 * 
From 
 cities AS C
 INNER JOIN weather AS W ON C.city = W.city
Where 
 C.city = 'A'

In the example you mention, this query is used:

SELECT *
FROM weather, cities
WHERE city = name;

answered Aug 8 at 13:23

EzLo

6,46561326

@EzLo I'm sorry you're proposed answer doesn't give correct results: "A" "(12,10)" "A" -5 40 "25" "2018-01-10" "A" "(12,10)" "B" 5 45 "15" "2018-02-10"
â€“Â Zakhar
Aug 8 at 14:16

1

@Zakhar City B is being listed because you replaced name which is a column with A which is a value, thus losing the join between both tables.
â€“Â EzLo
Aug 8 at 14:19

add a commentÂ |Â

up vote
5
down vote

Both tables cities and weather have a column called city. On your WHERE clause you filter city = 'A', which table's city is it refering to?

You can tell the engine which one you want to filter by preceding the column with it's table name:

Select * From cities, weather Where cities.city = 'A'

You can also refer to tables with alias:

Select * 
From cities AS C, weather AS W 
Where C.city = 'A'

Select 
 * 
From 
 cities AS C
 INNER JOIN weather AS W ON C.city = W.city
Where 
 C.city = 'A'

In the example you mention, this query is used:

SELECT *
FROM weather, cities
WHERE city = name;

answered Aug 8 at 13:23

EzLo

6,46561326

Both tables cities and weather have a column called city. On your WHERE clause you filter city = 'A', which table's city is it refering to?

You can tell the engine which one you want to filter by preceding the column with it's table name:

Select * From cities, weather Where cities.city = 'A'

You can also refer to tables with alias:

Select * 
From cities AS C, weather AS W 
Where C.city = 'A'

Select 
 * 
From 
 cities AS C
 INNER JOIN weather AS W ON C.city = W.city
Where 
 C.city = 'A'

In the example you mention, this query is used:

SELECT *
FROM weather, cities
WHERE city = name;

answered Aug 8 at 13:23

EzLo

6,46561326

answered Aug 8 at 13:23

EzLo

6,46561326

answered Aug 8 at 13:23

EzLo

6,46561326

answered Aug 8 at 13:23

EzLo

6,46561326

@EzLo I'm sorry you're proposed answer doesn't give correct results: "A" "(12,10)" "A" -5 40 "25" "2018-01-10" "A" "(12,10)" "B" 5 45 "15" "2018-02-10"
â€“Â Zakhar
Aug 8 at 14:16

1

@Zakhar City B is being listed because you replaced name which is a column with A which is a value, thus losing the join between both tables.
â€“Â EzLo
Aug 8 at 14:19

add a commentÂ |Â

@EzLo I'm sorry you're proposed answer doesn't give correct results: "A" "(12,10)" "A" -5 40 "25" "2018-01-10" "A" "(12,10)" "B" 5 45 "15" "2018-02-10"
â€“Â Zakhar
Aug 8 at 14:16

1

@Zakhar City B is being listed because you replaced name which is a column with A which is a value, thus losing the join between both tables.
â€“Â EzLo
Aug 8 at 14:19

@EzLo I'm sorry you're proposed answer doesn't give correct results: "A" "(12,10)" "A" -5 40 "25" "2018-01-10" "A" "(12,10)" "B" 5 45 "15" "2018-02-10"
â€“Â Zakhar
Aug 8 at 14:16

@Zakhar City B is being listed because you replaced name which is a column with A which is a value, thus losing the join between both tables.
â€“Â EzLo
Aug 8 at 14:19

add a commentÂ |Â

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky