mysql count, distinct, join? COnfusion

Question

hello i have 2 tables:

tblItems 
ID           |     orderID             | productID
1                   1                     2
2                   1                     2
3                   2                     1
4                   3                     2

tblProducts
productID    |     productName
1                   ABC
2                   DEF

im attempting to find the most popular Product based on whats in "tblItems", and display the product Name and the number of times it appears in the tblItems table.

i can get mysql to count up the total like:

$sql="SELECT COUNT(productID) AS CountProductID FROM tblItems";

but i can't figure out how to join the products table on..if i try LEFT JOIN the query goes horribly wrong

hopefully thats not too confusing..thankss

Answer 1

I think you may be looking for something like:

SELECT tblProducts.productName, COUNT(tblItems.ID)
FROM tblProducts
LEFT JOIN tblItems USING(productID)
GROUP BY tblProducts.productID

Answer 2

Are you simply trying to find the count of orders by product like so:

Select P.ProductName, Count(*)
From tblItems As I
    Join tblProducts As P
        On P.ProductId = I.ProductId
Group By P.ProductName

Answer 3

SELECT count(i.productID) AS cnt, p.productName FROM tblItems AS i
LEFT JOIN tblProducts AS p ON p.productID = i.productID
GROUP BY i.productID
ORDER BY cnt desc