mysql - sql使用内部联接连接三个表

在我的数据库中，我有3个名为items、manufacturers和items_manufacturers的表。manufacturers与
我的items_manufacturers表

 +---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| code          | varchar(25)  | NO   | MUL | NULL    |                |
| item_category | varchar(100) | NO   |     | NULL    |                |
| item_desc     | varchar(500) | NO   |     | NULL    |                |
| reorder_point | int(11)      | NO   |     | NULL    |                |
| unit          | varchar(45)  | NO   |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

我的items表

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| code       | varchar(25)  | NO   |     | NULL    |                |
| name       | varchar(250) | NO   |     | NULL    |                |
| address    | varchar(750) | NO   |     | NULL    |                |
| contact_no | varchar(50)  | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

我的manufacturers表

+-----------------+---------------+------+-----+---------+----------------+
| Field           | Type          | Null | Key | Default | Extra          |
+-----------------+---------------+------+-----+---------+----------------+
| id              | bigint(20)    | NO   | PRI | NULL    | auto_increment |
| item_id         | bigint(20)    | NO   | MUL | NULL    |                |
| manufacturer_id | bigint(20)    | NO   | MUL | NULL    |                |
| unit_cost       | decimal(20,2) | NO   |     | NULL    |                |
| vendor_id       | bigint(20)    | NO   |     | NULL    |                |
+-----------------+---------------+------+-----+---------+----------------+

在我的结果表中，我想要items_manufacturers表和items_id中制造商的items_desc、name、manufacturers。我的关系是

items.id=items_manufacturers.item_id and
manufacturers.id=items_manufacturers.manufacturer_id.

我试着对三个表使用内部连接，但没有成功。
我试过的问题

select
  items_manufacturers.id,
  items.item_desc,
  item_manufacturers.manufacturer_id,
  manufacturer.name
from items_manufacturers
INNER JOIN items ON items_manufacturers.item_id=items.id
INNER JOIN manufacturers ON items_manufacturers.manufacturer_id=manufacturers.id

有谁能帮我这个忙，我已经很长时间没睡了

最佳答案

我用了下面的代码，得到了你想要的结果。此代码可以解决您的问题：

select a.name,b.manufacturer_id,c.id,c.item_desc
from manufacturers as a
inner join
item_manufacturers as b
on b.manufacturer_id=a.id
inner join item as c
on c.id=b.item_id

关于mysql - sql使用内部联接连接三个表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20177062/