tarek fellah tarek fellah - 1 month ago 15
MySQL Question

Get most sold products with a specified one

I have a table order_detail with this informations

id_order id_product
1 2
1 3
2 2
2 4
2 5
2 3
3 2
3 1
4 2
4 3
4 1
4 6


I would like to get most sold products(the first 5 elements) with the current product let's say product id 2

I tried this but it returns one wrong result

SELECT od2.product_id, count(od2.`product_id`) FROM `ps_order_detail` od1
LEFT JOIN ps_order_detail od2 ON od1.id_order = od2.id_order where
od2.product_id != od1.product_id AND od1.product_id=2


The result should be

product_id count(od2.`product_id`)
3 3
4 2
1 1
5 1
6 1

Answer

Your query is on the right track. Mostly, you are missing a group by:

select od.product_id, count(od2.id_order) as NumTimesWith2
from ps_order_detail od left join
     ps_order_detail od2
     on od.id_order = od2.id_order and
        od2.product_id = 2 
where od.product_id <> 2
group by od.product_id
order by count(od2.id_order) desc;

If you want only one such product, then add a limit 1 to the query.

Also, this assumes that products are not repeated inside orders. If they can be, you can quickly get a better count using count(distinct od.id_order)).