sql joining three tables using inner joins
join, mysql, sql
Solution
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
Problem
in my database I have 3 tables named `items`, `manufacturers` and `items_manufacturers`. `manufacturers` has a HAS:MANY relation with `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 ``` +-----------------+---------------+------+-----+---------+----------------+ | 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_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