in my database I have 3 tables named items, manufacturers and items_manufacturers. manufacturers has a HAS:MANY relation with items_manufacturers
在我的数据库中有3个表，分别是items, manufacturer和items_manufacturers。制造商与items_manufacturers有:很多关系
My items table
我的项目表
+---------------+--------------+------+-----+---------+----------------+
| 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 | |
+---------------+--------------+------+-----+---------+----------------+
My manufacturers table
我的厂商表
+------------+--------------+------+-----+---------+----------------+
| 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 | |
+------------+--------------+------+-----+---------+----------------+
My items_manufacturers table
我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 | |
+-----------------+---------------+------+-----+---------+----------------+
In my result table I want items_id, items_desc, name of manufacturer from manufacturers table and manufacturer_id. The relation I have is
在我的结果表中，我想要items_id、items_desc、来自制造商表的制造商名称和manufacturer_id。我的关系是
items.id=items_manufacturers.item_id and
manufacturers.id=items_manufacturers.manufacturer_id.
I tried using inner joins for three tables but not working. The query I tried
我试着用内连接三个表，但不工作。查询我试着
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
Anybody kindly help me with this, I am stuck up from a long time
有谁愿意帮我的忙，我被困了很久。
1 个解决方案
#1
5
I used this following code and got the result you were trying to get. This code may solve your problem:
我用了下面的代码，得到了你想要得到的结果。这段代码可以解决您的问题:
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
#1
5
I used this following code and got the result you were trying to get. This code may solve your problem:
我用了下面的代码，得到了你想要得到的结果。这段代码可以解决您的问题:
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