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: 
You have to use `INNER JOIN` between *products* and *orderitem* table on the \*p\_id\* field because that is the common field tying both the tables. `INNER JOIN` will fetch only the matching records between the two tables. The join will match all the matching records, since the ordereditem values are already grouped by \*p\_id\* column, you will see all the unique product info from products table along with the order count based on the output of COUNT(p\_id) information.

[**Click here to view the demo at SQL Fiddle.**](http://sqlfiddle.com/#!2/bb834/5)

*Script*:

```
CREATE TABLE products 
(
    p_id            INT NOT NULL 
  , p_name          VARCHAR(20) NOT NULL
  , p_image_path    VARCHAR(255) NOT NULL  
);

CREATE TABLE orderitem
(
    orderid INT NOT NULL
  , p_id    INT NOT NULL
);

INSERT INTO products (p_id, p_name, p_image_path) VALUES 
    (1, 'product 1', '~\image 1'),
    (2, 'product 2', '~\image 2'),
    (3, 'product 3', '~\image 3');

INSERT INTO orderitem (orderid, p_id) VALUES 
    (1, 2),
    (1, 1),
    (1, 3),
    (1, 2),
    (1, 2),
    (1, 1);

SELECT      p.p_id
        ,   p.p_name
        ,   p.p_image_path
        ,   COUNT(o.p_id) AS orderedcount
FROM        products p 
INNER JOIN  orderitem o
ON          p.p_id = o.p_id
GROUP BY    p.p_id
ORDER BY    orderedcount DESC;

```

*Output*:

```
P_ID P_NAME      P_IMAGE_PATH ORDEREDCOUNT
---- ----------  ------------ ------------
2    product 2   ~image 2         3
1    product 1   ~image 1         2
3    product 3   ~image 3         1

```
```
SELECT p_id, p_name, p_price, p_image_path, COUNT( p_id ) AS p_id_count
FROM orderitem
inner join products p on p.id = p_id
GROUP BY p_id
ORDER BY p_id_count $direction

```