How to get product ids of specific category and not in other categories in Magento?

Question

I want to get product ids of a specific category and product ids not in other categories only in a specific category.

For example,

category ids = 3, 4, 10 and product ids = 10, 12, 14 and category id 3 belong to product id 10 and category id 4 belong to product ids 10, 12, 14 and category id 10 belong to product id 14.

When I filter on category id = 4, it returns product ids 10, 12 and 14, but I want to return only product id 12 because I only want the product id which is not in other categories. In my filter category id 4 returns product ids 10,12 and 14 but I only want to product id 4 because 10 and 14 belong to other categories which I don't want. I only want to product id 12 in filter. Which filter I used ?

$catIds = array(4);
$notCatIds = array(3, 10);
$productCollection = Mage::getResourceModel('catalog/product_collection')
    ->setStoreId(0)
    ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id=entity_id', null, 'left')
    ->addAttributeToFilter('category_id', array('in' => $notCatIds))
    ->addAttributeToFilter('category_id', array('in' => $catIds))
    ->addAttributeToFilter('category_id', array('nin' => $notCatIds))
    ->addAttributeToFilter('category_id', array('nin' => array(2)))// 2 is default category id
    ->addAttributeToSelect('*');
$productCollection->getSelect()->group('product_id')->distinct(true);

echo '<pre>';
print_r($productCollection->getData());

Answer 1

Step1: all categories ids form Category and then

$categiesIDs= Mage::getResourceModel('catalog/category_collection')->getAllIds();

Step2: exclude your category[12] from $categiesIDs uset

Step3: Then Product collection filter by Categories ids.

$productCollection->getSelect()
    ->join(
        array('at_category_id' => $productCollection->getTable('catalog/category_product')),
        'at_category_id.product_id=e.entity_id',
        array(
            'at_category_id.category_id',
            'cattotal'=> new Zend_Db_Expr('COUNT(*)')
        )
    ->group('e.entity_id');

Answer 2

The below query will give some insights to you.

From your scenario, one must say you need to list the products belongs to a single category. So below query will satisfy your requirements.

It's a bit of raw if you can rewrite Magento way better.

Note: The catlist field added to see what are all the categories attached to a product in a comma separated way.

$catIds = array(4);
$productCollection = Mage::getResourceModel('catalog/product_collection');
$productCollection->getSelect()
->join(
    array('at_category_id' => $productCollection->getTable('catalog/category_product')),
    'at_category_id.product_id=e.entity_id',
    array(
        'at_category_id.category_id',
        'cattotal'=> new Zend_Db_Expr('(SELECT COUNT(product_id) FROM '.$productCollection->getTable('catalog/category_product').' WHERE product_id=e.entity_id AND category_id NOT IN ('.implode(",", $catIds).'))'),
        'catlist'=> new Zend_Db_Expr('GROUP_CONCAT(at_category_id.category_id)')
    ))
->where("at_category_id.category_id IN (".implode(",",$catIds).")")
->having("cattotal=1")
->group('e.entity_id');

Adapting some elements from Amit Bera's answer.