mysql - How to order result according to another table in mysql

Question

I want to select products in order to best selling items according to another table.

select product_id
       from wp_product_details_fields
       where field in ( 'brand', 'model')
       group by product_id
       having  (  max(`field` = 'brand' and `value` = 'Intel') = 1 
                  OR
                  max(`field` = 'model' and `value` = 'Core i7') = 1  ) 
       order by (SELECT COUNT(*) AS magnitude 
                        FROM wp_order_items 
                        GROUP BY product_id 
                        ORDER BY magnitude DESC)

I've got this Error:

MySQL said: Subquery returns more than 1 row

标签： mysqlsqlsql-order-byinner-joinwhere-clause

Answer 1

The query:

SELECT product_id
FROM (your complex query w/o ORDER BY) wpd
Left JOIN wp_order_items USING (product_id)
GROUP BY product_id
ORDER BY COUNT(*) /* DESC */