Question: I want to select the most bought items from table `orderitems` and then get the details of those items and sort DESC

I have the following which returns the most bought items but i can't figure out how to return the `p_name` `p_price` and `p_image_path` of each item(p\_id) from `products` table

```
SELECT      p_id
        ,   COUNT( p_id ) AS p_id_count
FROM        orderitem
GROUP BY    p_id
ORDER BY    p_id_count $direction

```

I hope this makes sense

Answer: 
I am assuming the primary key of the product is id and which has reference with p\_id of orderitem 

```
SELECT o.p_id, COUNT( o.p_id ) AS p_id_count, p.p_name,p.p_price,p.p_image_path 
        FROM orderitem o
    INNER JOIN products p ON p.id = o.p_id 
        GROUP BY o.p_id
        ORDER BY o.p_id_count $direction

```
You need to join the products table with the orderitem table.

```
SELECT p_id, p_name, p_price, COUNT( p_id ) AS p_id_count
    FROM orderitem
    LEFT JOIN products ON (orderitem.p_id = products.id)
    GROUP BY p_id, p_name, p_price
    ORDER BY p_id_count $direction

```