SELECT * from(
Select products.id as productId,products.name,products.nameEn,productvariety.image,productvariety.soldCount as soldCount,brands.name as producerName,brands.nameEn as producerNameEn
,productvariety.discount,productvariety.id as pvid,productvariety.price,productcategorylink.cid,productcategory.id as pcatid,productvariety.barcode,productvariety.outofstock as offstock,products.image as Pimg from products
INNER JOIN brands ON products.brandId=brands.id
INNER JOIN productvariety ON products.id=productvariety.productId
INNER JOIN productcategorylink ON products.id=productcategorylink.pid
INNER JOIN productcategory ON productcategory.id=productcategorylink.cid
where products.isDisabled='no' AND products.isRemoved='no' AND productvariety.isDisabled ='no' AND productvariety.outofstock='no' AND (productcategorylink.cid=27 || productcategorylink.parentZero=27 || productcategorylink.parentTop=27 )
group by productvariety.productId
UNION
Select products.id as productId,products.name,products.nameEn,productvariety.image,productvariety.soldCount as soldCount,brands.name as producerName,brands.nameEn as producerNameEn
,productvariety.discount,productvariety.id as pvid,productvariety.price,productcategorylink.cid,productcategory.id as pcatid,productvariety.barcode,productvariety.outofstock as offstock,products.image as Pimg from products
INNER JOIN brands ON products.brandId=brands.id
INNER JOIN productvariety ON products.id=productvariety.productId
INNER JOIN productcategorylink ON products.id=productcategorylink.pid
INNER JOIN productcategory ON productcategory.id=productcategorylink.cid
where products.isDisabled='no' AND products.isRemoved='no' AND productvariety.isDisabled ='no' AND productvariety.outofstock='yes' AND (productcategorylink.cid=27 || productcategorylink.parentZero=27 || productcategorylink.parentTop=27 )
group by productvariety.productId
) t group by productId
order by offstock asc , soldCount desc LIMIT 0,100
لباس شلوار | بیست مارت
