SELECT name, tarif.count_offers as count FROM positions 
LEFT JOIN users ON users.id=positions.user_id
LEFT JOIN tarif ON tarif.id=user.tarif 
WHERE {хитрое условие чтобы вывелось только count записей пользователя}
LIMIT 0,10

SELECT  * FROM pricelist_position AS pos
            INNER JOIN pricelist AS req ON req.id=pos.id_pricelist
            LEFT web_user_attributes_extended AS extend ON extend.internalKey=req.uid
            LEFT JOIN tarif AS tarif ON tarif.id=extend.tarif
            WHERE pos.id>0 AND pos.price>0  AND category_id=8 AND ((category_id='6' AND type='0') OR (category_id='7' AND type='1') OR (category_id='8' AND type='1')) AND price>0 AND status=1 AND price>0 AND ( address LIKE '%Россия%')
            ORDER BY advertsUp DESC,  pos.id DESC, category_id DESC, group_id ASC, subgroup_id ASC, subsubgroup_id ASC,  pos_id ASC LIMIT 0 , 10