“字段列表”中的未知列“e.eid”

Question

使用MySQL并获取此错误，下面是我的works_on表中的代码片段&插入

 DROP TABLE IF EXISTS `works_on`;
CREATE TABLE works_on(
eid INT NOT NULL,
pid INT NOT NULL,
start_date DATE,
PRIMARY KEY (eid, pid),
FOREIGN KEY (eid) REFERENCES employee(id),
FOREIGN KEY (pid) REFERENCES project(id)
);

INSERT INTO works_on
(eid, pid, start_date)
SELECT
e.eid AS eid, p.pid AS eid, '2012-01-01'
FROM project p, employee e
WHERE e.first_name = 'Adam' AND e.last_name = 'Lowd'
AND p.name = 'Diamond';

Answer 1

我猜你打算：

INSERT INTO works_on (eid, pid, start_date)
    SELECT e.id AS eid, p.id AS pid, '2012-01-01'
    FROM project p CROSS JOIN
         employee e
    WHERE e.first_name = 'Adam' AND e.last_name = 'Lowd' AND p.name = 'Diamond';