SELECT * FROM catalog_category_entity AS catalog_category_table
LEFT JOIN (SELECT DISTINCT(product_id),category_id FROM catalog_category_product GROUP BY product_id) catalog_category_product_table ON catalog_category_product_table.category_id = catalog_category_table.entity_id

$subquery = new Zend_Db_Expr("(
    SELECT DISTINCT(product_id), category_id
    FROM `catalog_category_product`
    GROUP BY product_id
)");
$collection = Mage::getModel('catalog/category')->getCollection();
$collection->getSelect()
    ->joinLeft(
        $subquery,
        't.category_id = e.entity_id'
    );