0
CREATE DATABASE IF NOT EXISTS EdocodeDB2;
USE EdocodeDB2;

-- create
CREATE TABLE EMPLOYEE (
  IdNum int,
  Name varchar(255),
  PRIMARY KEY(IdNum)
);

-- insert
INSERT INTO EMPLOYEE VALUES (1, 'Kosuke');
INSERT INTO EMPLOYEE VALUES (2, 'Keisuke');
INSERT INTO EMPLOYEE VALUES (3, 'Icchy');

CREATE TABLE INSURANCE (
  IdInsurance int,
  Name varchar(255),
  Employee_id int,
  PRIMARY KEY(IdInsurance)
);

-- insert
INSERT INTO INSURANCE
VALUES (1, 'CAR', 1);

INSERT INTO INSURANCE
VALUES (2, 'CAR', 3);

-- left join
SELECT IdNum, Name
FROM EMPLOYEE
LEFT JOIN INSURANCE
ON EMPLOYEE.IdNum = INSURANCE.Employee_id;

Column 'Name' in field list is ambiguous but I'm clearly saying FROM EMPLOYEE

I had to change the Id field to IdNum and IdInsurance.

1 Answer 1

4

You have two columns called Name, one in table EMPLOYEE, the other in table INSURANCE. How does MySQL know which table you want to get the column from?

You should specify the table names to clear up the ambiguity:

SELECT EMPLOYEE.IdNum, INSURANCE.Name -- Or EMPLOYEE.Name if you mean the column in the EMPLOYEE table
FROM EMPLOYEE
LEFT JOIN INSURANCE
ON EMPLOYEE.IdNum = INSURANCE.Employee_id;

"but I'm clearly saying FROM EMPLOYEE" - That's not how MySQL works. The first table in the FROM clause doesn't automatically imply which instance of the columns you're referring to. When there's a column with the same name in multiple tables in the FROM / JOIN clauses, then the table name (or an alias of that table) must be explicitly specified in the SELECT clause. It's also good practice to do so anyway, so someone who sees your query immediately knows which table each column belongs to.

1
  • Thanks! I got what I wanted using your command and adding a few columns: create table MajorTable2 as SELECT EMPLOYEE.IdNum, EMPLOYEE.NameEmp, INSURANCE.IdInsurance, INSURANCE.NameInsurance FROM EMPLOYEE LEFT JOIN INSURANCE ON EMPLOYEE.IdNum = INSURANCE.Employee_id; Aug 6 at 4:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.