MYSQL:错误 1054 (42S22): 'id_employee' 中的未知列 'from clause'

Question

我有两张 table 。

i) 订单详情:

CREATE TABLE `order_details` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `content` text,
  `id_employee` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_id_employee` (`id_employee`),
  CONSTRAINT `FK_id_employee` FOREIGN KEY (`id_employee`) REFERENCES `employees` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

ii) 员工:

 CREATE TABLE `employees` (   `id` INT(11) NOT NULL AUTO_INCREMENT,   `firstname` text NOT NULL,   `lastname` text NOT NULL,   `salary` FLOAT DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 ;

我想获取员工 3 负责的名字、姓氏和订单详细信息。我用过这个:

SELECT lastname, firstname, content FROM order_details INNER JOIN employees USING (id_employee) WHERE id_employee = 3;

但我收到此错误消息:ERROR 1054 (42S22): Unknown column 'id_employee' in 'from clause' 并且不知道它来自哪里:(

Answer 1

当您使用 USING 时，列的名称在两个表中必须相同。 employee 表中没有 id_employee。所以你应该改用它，它使用 ON 条件来连接两个表:

SELECT e.lastname, e.firstnam, o.content 
FROM order_details o
INNER JOIN employees e
  ON  o.id_employee = e.id 
WHERE o.id_employee = 3;

来自 MySQL docs :

The USING(column_list) clause names a list of columns that must exist in both tables. If tables a and b both contain columns c1, c2, and c3, the following join compares corresponding columns from the two tables