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

Question

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

有 HAS:MANY 关系

我的项目表

 +---------------+--------------+------+-----+---------+----------------+
| 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    |                |
+---------------+--------------+------+-----+---------+----------------+

我的制造商表

+------------+--------------+------+-----+---------+----------------+
| 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    |                |
+------------+--------------+------+-----+---------+----------------+

我的items_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    |                |
+-----------------+---------------+------+-----+---------+----------------+

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

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 

任何人都好心帮助我，我很长一段时间都被困住了

Answer 1

我使用了以下代码并得到了您想要的结果。此代码可能会解决您的问题:

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