这个mysql连接怎么写

Question

我有下表

mysql> desc items;
+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| id                | int(11)      | NO   | PRI | NULL    | auto_increment |
| code              | varchar(255) | YES  |     | NULL    |                |
| name              | varchar(255) | YES  |     | NULL    |                |
| price             | float        | YES  |     | NULL    |                |
| ingredient_id     | int(11)      | YES  |     | NULL    |                |
| ingredient_id2    | int(11)      | YES  |     | 0       |                |
| ingredient_id3    | int(11)      | YES  |     | 0       |                |
+-------------------+--------------+------+-----+---------+----------------+

，我的成分表

mysql> desc ingredients;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| name         | varchar(255) | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

和 ingredient_id、成分_id2、成分_id3 列是“成分”表的外键，我的要求是创建一个选择查询来显示项目和成分，如下

select id, code, name, price, name as ingredient_name1, name as ingredient_name2, name as ingredient_name3

但我不这样做知道如何连接同一个表3次，有人可以帮助我理解这个连接条件

Answer 1

select items.id, 
code, 
name, 
price, 
in_1.name as ingredient_name1, 
in_2.name as ingredient_name2, 
in_3.name as ingredient_name3
from items
  join ingredients as in_1 on items.ingredient_id = in_1.id
  join ingredients as in_2 on items.ingredient_id2 = in_2.id        
  join ingredients as in_3 on items.ingredient_id3 = in_3.id        

Answer 2

尝试

SELECT i.id, i.name, i.code, i.price, 
       ig1.name AS igName1,ig2.name AS igName2,ig3.name as igName3
FROM items i 
INNER JOIN ingredients ig1 ON ig1.id=i.ingredient_id
INNER JOIN ingredients ig2 ON ig2.id=i.ingredient_id2
INNER JOIN ingredients ig3 ON ig3.id=i.ingredient_id3

Answer 3

您好，您可以使用具有不同别名的同一个表。

成分 AS In1
成分 AS In2
成分 AS In3

Answer 4

我认为这就是您正在寻找的：

select it.id, it.code, it.name, it.price,
    ing1.name as name1, ing2.name as name2, ing3.name as name3
from items it, ingredients ing1, ingredients ing2, ingredients ing2
where it.ingredient_id = ing1.id
    and it.ingredient_id2 = ing2.id
    and it.ingredient_id3 = ing3.id