How to show products that are in two categories?

Question

How do I get products that are in any sub-category AND category b, ignoring all other products that are in one or the other?

$cats = explode(',', $_category->getChildren()); // all sub-category products // array(4,5,6)
$cats[] = 15; // Specific brand category //$cats = array(4,5,6,15)
$bottomRangeCollection = Mage::getModel('catalog/product')->getCollection()
                                 ->addAttributeToSelect('*')
                                 ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id = entity_id', null, 'left outer')
                                 ->addAttributeToFilter('category_id', array('in' => array('finset' => $cats)))
$bottomRangeCollection->getSelect()->group('e.entity_id');

But this is returning all products in all categories.

How do I filter the products to categories 4 and 15, or 5 and 15, or 6 and 15.

edit

Hacky solution:

Remove the specific brand category id from the $cats array, so we just get all products from the sub-categories, then we do the below - a foreach that adds products that are in the brand category to an array.

//if product is also in brand category, add it to our little array
$rangeProducts = array();
foreach($bottomRangeCollection as $product) {
    if(in_array(15, $product->getCategoryIds())) {
        $rangeProducts[] = $product;
    }
}
unset($bottomRangeCollection); //throw out the garbage.

There has to be a way to do this in Magento.

edit

I have the following category structure:

root
|-Bathrooms
|--Sinks
|--Cabinets
|--...
|-Bedrooms
|--Lights
|--Beds
|--...
|-Ranges
|--Deor
|--Puro
|--...

Products will be put into a relevant category (such as Sinks) and into a Range category (such as Deor). What I am trying to do, is get the range products (the Deor products in this example) that are also in the Bathroom sub-categories (both Sinks and Cabinets), BUT ignoring all other range products, and any products in the bathroom sub-categories that is not in a Range category (which shouldn't be any. But, you know, covering all bases).

Answer 1

It works if you move the filter to the join condition. In your special case you need two separate joins, one to match the products in your subcategories and one to match the special brand category.

$cats = explode(',', $_category->getChildren());
brandCategoryId = 15;

$bottomRangeCollection = Mage::getModel('catalog/product')->getCollection()
    ->addAttributeToSelect('*')
    ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id = entity_id', "at_category_id.category_id IN ($cats)", 'right')
    ->joinField('brand_category_id', 'catalog/category_product', 'category_id', 'product_id = entity_id', "at_brand_category_id.category_id = $brandCategoryId", 'right')
$bottomRangeCollection->getSelect()->group('e.entity_id');

Note that the join conditions are raw SQL, there is no quoting involved. If you can not absolutely be sure that the category id variables are just numbers, use $connection->quoteInto() to escape the values.