For some reason query I put directly to DB works, but when set in Joomla syntax is returning only 1 result, and there should be a lot more...
Clear Mysql query:
SELECT vmp.virtuemart_product_id as 'product_id', vmp.product_parent_id as 'parent_id', vmp.product_sku as 'sku', vmp.product_in_stock as 'stock', vmp.published as 'published', pc.virtuemart_category_id as "category" FROM `mus_virtuemart_products` as vmp JOIN mus_virtuemart_product_categories as pc ON pc.virtuemart_product_id=vmp.product_parent_id WHERE vmp.product_parent_id>0 AND pc.virtuemart_category_id=105;
$query->select(array( 'vmp.virtuemart_product_id as product_id', 'vmp.product_parent_id as parent_id', 'vmp.product_sku as sku', 'vmp.product_in_stock as stock', 'vmp.published as published', 'pc.virtuemart_category_id as category' )) ->from($db->quoteName('#__virtuemart_products','vmp')) ->join('INNER', $db->quoteName('#__virtuemart_product_categories','pc') . ' ON (' . $db->quoteName('pc.virtuemart_product_id') . ' = ' . $db->quoteName('vmp.virtuemart_product_id') . ')') ->where($db->quoteName('pc.virtuemart_category_id') . ' = '. $db->quote($category). ' AND '. $db->quoteName('vmp.product_parent_id') . ' >0');
I have tried various combinations, and am always getting only 1 result. I have limited to only 1 WHERE condition that I possible casuse problem:
->where($db->quoteName('vmp.product_parent_id') . ' >0');
I have tried to set "zero" as
$zero="0", but didn't change anything...
Thanks all for help