mysql 连接三个表_mysql – 使用内部联接连接三个表的SQL

在我的数据库中,我有3个表名为items,manufacturer和items_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 | |

+---------------+--------------+------+-----+---------+----------------+

我的制造商表

+------------+--------------+------+-----+---------+----------------+

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

+-----------------+---------------+------+-----+---------+----------------+

在我的结果表中,我想要items_id,items_desc,制造商名称和manufacturer_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

有人帮我这个,很长一段时间我都被困住了

解决方法:

我使用了以下代码,得到了你想要获得的结果.此代码可以解决您的问题：

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

标签：sql,mysql,join

来源： https://codeday.me/bug/20190831/1774726.html