PHP MySQL join 3 tables and output result

Question

I have three tables as follows:

Items:

-----------------------------------------------------
|  itemID  |  itemName  |  categoryID  |  sellerID  |
-----------------------------------------------------
|  1       |  item1     |  c1          |  s1        |
|  2       |  item2     |  c1          |  s2        |
|  3       |  item3     |  c3          |  s2        |
|  4       |  item4     |  c2          |  s3        |
-----------------------------------------------------

Categories:

---------------------------------
|  categoryID  |  categoryName  |
---------------------------------
|  c1          |  category1     |
|  c2          |  category2     |
|  c3          |  category3     |
---------------------------------

Seller:

-----------------------------
|  sellerID  |  sellerName  |
-----------------------------
|  s1        |  seller1     |
|  s2        |  seller2     |
|  s3        |  seller3     |
-----------------------------

I want to select items from the Items table where categoryID is c1 and display it as follows:

----------------------------------------------
|  itemName  |  categoryName  |  sellerName  |
----------------------------------------------
|  item1     |  category1     |  seller1     |
|  item2     |  category1     |  seller2     |
----------------------------------------------

I can't figure out how to do it. Kindly help me out.

score 3 · Accepted Answer · answered Aug 08 '14 at 13:12

3

Have a look at JOIN:

SELECT i.itemName, c.categoryName, s.sellerName
FROM Items i
JOIN Categories c
  ON c.id = i.categoryID
JOIN Seller s
  ON s.id = i.sellerID
WHERE i.categoryID = 'c1'

answered Aug 08 '14 at 13:12

Ende Neu

15,581
5
57
68

It worked. Thanks a lot. I had written almost the same query. The only difference was that I was using `INNER JOIN` instead of `JOIN`. I guess I have to research it thoroughly and understand the basic difference between all the different types of joins. Thanks again. – user3625048 Aug 08 '14 at 13:31
You're welcome, check [this](http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join) question about join differences. – Ende Neu Aug 08 '14 at 13:33

score 0 · Answer 2 · answered Aug 08 '14 at 13:13

0

Something like this:

select items.itemName, categories.categoryName, seller.sellerName
from items
join categories on items.categoryId = categories.categoryId
join seller on items.sellerId = seller.sellerId
where categories.categoryId = ?

answered Aug 08 '14 at 13:13

duffymo

305,152
44
369
561

score 0 · Answer 3 · answered Aug 08 '14 at 13:13

0

SELECT itemName, categories.categoryName, seller.sellerName 
FROM items 
INNER JOIN categories ON items.categoryID = categories.categoryID 
INNER JOIN seller ON items.sellerID = seller.sellerID 
WHERE items.categoryID = 'c1'

answered Aug 08 '14 at 13:13

Alex

478
2
11

score 0 · Answer 4 · answered Aug 08 '14 at 13:14

0

Try this:

$strSql = 'SELECT a.`itemName`,b.`categoryName`,c.`sellerName` FROM `Items` a INNER JOIN `Categories` b ON a.`categoryID` = b.`categoryID`
         INNER JOIN `Seller` c ON a.`sellerID` = c.`sellerID` WHERE a.`categoryID` = "c1" ';

answered Aug 08 '14 at 13:14

Awlad Liton

9,366
2
27
53

score 0 · Answer 5 · answered Aug 08 '14 at 13:16

It's just a matter of creating the correct joins. In this case, you need to attach the tables to two different things. The syntax should look like this:

$stmt = 'SELECT a.itemName,b.categoryName,c.sellerName FROM `[itemsTable]`
    inner join a  on b.categoryID  = a.categoryID  
inner join a on c.sellerID = a.sellerID';

PHP MySQL join 3 tables and output result

5 Answers5