Ttdfjt

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());

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');

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.