Exclude Products With Tier Prices From Catalog Rule Collection

Question

When creating a Catalog Rule, it currently includes products that have tier prices.

I would like to do the direct opposite of the following code

$select->joinInner(
     array('product_tierprice' => 'catalog_product_entity_tier_price'),
     'product_tierprice.entity_id=rp.product_id',
     array()
 );

The above code will limit Catalog Rules to just products with tier prices and it works. Attempting to do the opposite causes errors.

What I want to do is rp.product_id's that are not present in catalog_product_entity_tier_price. Something along the lines of the following, but actually works

$select->joinLeft(
    array('product_tierprice' => 'catalog_product_entity_tier_price'),
    'product_tierprice.entity_id=rp.product_id',
     array()
);
$select->where('rp.product_id NOT IN (product_tierprice.entity_id)');

Answer 1

With indexing enabled, I used 'catalog_product_index_price', selecting rows where Tier_Price column was null for that particular website.

$select->joinInner(
    array('pt' => 'catalog_product_index_price'),
    'pt.entity_id=rp.product_id AND pt.tier_price IS NULL AND pt.website_id='.$websiteId,
    array('pt.customer_group_id')           
);
$select->where('pt.customer_group_id IN(rp.customer_group_id)');

This can also be changed to remove the WHERE clause to be

$select->joinInner(
    array('pt' => 'catalog_product_index_price'),
    'pt.entity_id=rp.product_id AND pt.customer_group_id IN(rp.customer_group_id) AND pt.tier_price IS NULL AND pt.website_id='.$websiteId,
    array()         
);