SELECT DISTINCT pm.*,pm.orig_product_code as pcode,pp.*,pt.short_title as short_title,pt.long_title as long_title from product_master pm INNER JOIN product_statuses ps ON pm.product_id = ps.product_id AND ps.channel_code = 'three' AND now() between ps.valid_from_date and ps.valid_to_date AND ps.status_code = 'C' INNER JOIN product_titles pt ON pm.product_id = pt.product_id INNER JOIN (select * from product_best_sellers where channel_code = 'kondor' and bs_index > 50) as bs ON pm.orig_product_code = bs.product_code INNER JOIN product_prices pp ON pm.product_id=pp.product_id AND pp.channel_code = 'three' AND pp.rrp > pp.display_price AND pp.display_price >= 0.99 AND now() between pp.valid_from_date and pp.valid_to_date WHERE pm.brand_id = 74 -- AND EXISTS ( -- select 1 from compatibility c -- INNER JOIN models m ON c.model_id = m.id -- INNER JOIN model_statuses ms ON m.id = ms.model_id and ms.channel_code = 'three' -- and ms.status_code = 'C' and now() between ms.valid_from_date and ms.valid_to_date -- where pm.product_id = c.product_id -- ) AND pm.stock_qty > '0' order by bs.bs_index desc, pm.date_introduced desc LIMIT 3