Get Disabled Products Quantities by Sku And Id

Question

is this possible? I can't restructure the tables over again every time I need to run a certain dependent function.

        $catSQL = "SELECT *
        FROM `catalog_product_entity_int`
        WHERE `attribute_id` =96 //our status attribute id
        AND `value` =2"; //disabled value

This gets all disabled products. However, this table is indexed by Id. I'm passed a number of sku's, how can I merge this table with something like catalog_product_entity and search by an array of skus? Essentially I need to get the Id of all disabled products and then check their quantities in the stock_item table.

Thanks to Marius below i've acomplished the above with the following:

function getDisabledIdsFromSkus($arraySkus){
        $conn = Mage::getSingleton('core/resource')->getConnection('core_write');

        $ComponentSkustring = $this->format($arraySkus);

        $anotherRoute = "SELECT k.`qty`
        FROM `catalog_product_entity` i
        LEFT JOIN catalog_product_entity_int e ON i.entity_id = e.entity_id
        RIGHT JOIN cataloginventory_stock_item k ON i.entity_id = k.item_id
        WHERE e.`attribute_id` =96
        AND e.`value` =2
        AND i.`sku` IN ($string)
        ORDER BY i.`sku` ASC ";

        $componentQuantities = ($conn->fetchAll($anotherRoute));
        Mage::log(print_r($componentQuantities, true));
            return;
}

private function format($array)
{
    // initialize variable
    $formattedArray = array();

    //for each element
    foreach ($array as $string) {
        //append quotes
        $formattedArray[] = "'" . $string . "'";
    }

    //convert formatted elements to string and return
    $formattedString = implode(',', $formattedArray);
    return $formattedString;
}

Answer 1

You cannot get the disabled products on frontend area when the flat tables are on.
The indexer that populates the flat tables takes into account only enabled products and associated to the websited being indexed.

[EDIT] Try with this select

SELECT *
FROM `catalog_product_entity_int` i
LEFT JOIN catalog_product_entity e ON e.entity_id = e.entity_id
WHERE i.`attribute_id` =96
AND i.`value` =2
AND e.sku IN ('sku1', 'sku2')