using children products I got the above error.
here the sql:
SELECT SQL_CALC_FOUND_ROWS *
FROM `jsgr_virtuemart_products_it_it` as l
JOIN `jsgr_virtuemart_products` AS p using (`virtuemart_product_id`)
LEFT JOIN `jsgr_virtuemart_product_categories` ON p.`virtuemart_product_id` = `jsgr_virtuemart_product_categories`.`virtuemart_product_id`
LEFT JOIN `jsgr_virtuemart_categories_it_it` as c ON c.`virtuemart_category_id` = `jsgr_virtuemart_product_categories`.`virtuemart_category_id`
LEFT JOIN `jsgr_virtuemart_product_manufacturers` ON p.`virtuemart_product_id` = `jsgr_virtuemart_product_manufacturers`.`virtuemart_product_id`
LEFT JOIN `jsgr_virtuemart_manufacturers_it_it` as m ON m.`virtuemart_manufacturer_id` = `jsgr_virtuemart_product_manufacturers`.`virtuemart_manufacturer_id`
LEFT JOIN `jsgr_virtuemart_product_shoppergroups` ON p.`virtuemart_product_id` = `jsgr_virtuemart_product_shoppergroups`.`virtuemart_product_id`
LEFT OUTER JOIN `jsgr_virtuemart_shoppergroups` as s ON s.`virtuemart_shoppergroup_id` = `jsgr_virtuemart_product_shoppergroups`.`virtuemart_shoppergroup_id`
LEFT OUTER JOIN `jsgr_virtuemart_products` children ON p.`virtuemart_product_id` = children.`product_parent_id`
WHERE (
(`product_name` LIKE "%paul%"
OR `product_sku` LIKE "%paul%"
OR `product_s_desc` LIKE "%paul%"
OR `category_name` LIKE "%paul%"
OR `category_description` LIKE "%paul%"
OR `mf_name` LIKE "%paul%")
AND p.`published`="1"
AND (
p.`product_in_stock` - p.`product_ordered` >"0"
OR children.`product_in_stock` - children.`product_ordered` > "0"
)
AND (
s.`virtuemart_shoppergroup_id`= "1"
OR s.`virtuemart_shoppergroup_id` IS NULL
)
)
group by p.`virtuemart_product_id`
ORDER BY product_name ASC LIMIT 0, 10
table 'virtuemart_products' is aliased twice, but there is not table prefix in where clause.
using
OR p.`product_sku` LIKE "%paul%"
it works.
now I try to work on a fix.
bye,
marco