Ambiguous column name in SQLite3, but ambiguity hard to avoid

SQLite3 on Linux Arch. One table of country names, which is going to be referenced through foreign keys by a different table listing the FIFA World Cup host country and champion for each edition. Ideally I will extend it to multiple fields like: runner-up, bronze medalist, fourth place, etc. The table with the list of countries remains the same, the other table will get data from the list of countries only twice for illustration’s sake, but the database should be able to sort the countries from the table of countries 13, 16, 24 and 32 times.

The table of countries is created thus:

CREATE TABLE countries (countries_id integer primary key autoincrement, country text);

The table of World Cup editions is created thus:

CREATE TABLE editions (
    edition_id integer primary key autoincrement,
    year integer not null,
    host integer not null,
    champion integer not null,
    foreign key (host) references countries (countries_id),
    foreign key (champion) references countries (countries_id)
);

Just four entries to populate the table of countries (it should include over 200 eventually):

INSERT INTO countries (country) VALUES('Uruguay');                                                   
INSERT INTO countries (country) VALUES('Italy');                                                     
INSERT INTO countries (country) VALUES('France');                                                    
INSERT INTO countries (country) VALUES('Brazil');

And just the first four editions of the tournament:

INSERT INTO editions (year,host,champion) VALUES(1930,1,1);                                          
INSERT INTO editions (year,host,champion) VALUES(1934,2,2);                                          
insert into editions (year, host, champion) values (1938,3,2);                                       
insert into editions (year, host, champion) values (1950,4,1);

This query works:

select editions.year, countries.country
from editions inner join countries on countries.countries_id=editions.host;

So far so good, but I get the "Parse error: ambiguous column name: countries.country" as soon as I attempt this SELECT statement:

select editions.year, countries.country as host_c, countries.country as champion_c
from
  editions
  inner join countries on countries.countries_id=editions.host
  inner join countries on countries.countries_id=editions.champion;

In a way, it seems to me just natural, since I am indeed querying twice from the table countries. But I would like to be able to perform such a query nonetheless.

The end result should be for SQLite to display a three-column table, showing the year of the World Cup edition, the name of the host country, and the country that won said edition. Other than querying countries.country twice, no other idea occurred to me.

What would the correct SELECT statement be so I can have the year of the edition, the host country and the champion country, using just one table with names of countries? Thank you very much.

>Solution :

Use aliases for the table names

select editions.year, H.country as host_c, C.country as champion_c
from
  editions
  inner join countries H
    on H.countries_id = editions.host
  inner join countries C
    on C.countries_id = editions.champion;

You can also use an alias for other tables to shorten the SQL

select E.year, H.country as host_c, C.country as champion_c
from
  editions E
  inner join countries H
    on H.countries_id = E.host
  inner join countries C
    on C.countries_id = E.champion;

aliases can also be introduced with the optional keyword AS:

...
from
  editions AS E

Leave a Reply