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