I have some problem with mysql query. Im trying to build table with all products ids, product names, manufacturers names and quantities per attribute in column per attribute Getting ids and names is easy. Problem starts when I try to JOIN columns per attribute and fill it with quantities. Some attributes are not assinged to product therefore dont have id_stock_available (quantity) in ps_stock_available In php code im getting attibutes ids from db into array. Than i build query based on that array. I wont be pasting whole query as its 465 lines long (including spaces between lines). Will paste beginning, some part of middle query and the and. If I run a query with just $i = 1 (t1.id_attribute1)
SELECT
ps_2product_lang.id_product,
ps_2product_lang.name,
ps_2category_lang.name AS manufacturer_name,
t1.id_attribute1
FROM ps_2product_lang
LEFT JOIN ps_2product
ON ps_2product_lang.id_product = ps_2product.id_product
LEFT JOIN ps_2category_lang
ON ps_2product.id_category_default = ps_2category_lang.id_category
LEFT JOIN ps_2category
ON ps_2category_lang.id_category = ps_2category.id_category
JOIN
(
SELECT
ps_2stock_available.id_product AS id_product,
ps_2stock_available.quantity AS id_attribute1
FROM ps_2stock_available
JOIN
ps_2product_attribute_combination
ON
ps_2stock_available.id_product_attribute = ps_2product_attribute_combination.id_product_attribute
WHERE
ps_2product_attribute_combination.id_attribute = 1
ORDER BY
id_product
)
AS t1
ON
ps_2product_lang.id_product = t1.id_product
WHERE ps_2product_lang.id_lang = 7
AND ps_2category_lang.id_lang = 7
AND ps_2product_lang.id_product > 12
AND (ps_2category.id_parent = 18 OR ps_2category.id_parent = 84)
ORDER BY
manufacturer_name, name
i get products with id_attribute = 1 with column attribute1 filled with data (quantities) Part below is being repeated in query as many times as there are attributes created in prestashop.
JOIN
(
SELECT
ps_2stock_available.id_product AS id_product,
ps_2stock_available.quantity AS id_attribute1
FROM ps_2stock_available
JOIN
ps_2product_attribute_combination
ON
ps_2stock_available.id_product_attribute = ps_2product_attribute_combination.id_product_attribute
WHERE
ps_2product_attribute_combination.id_attribute = 1
ORDER BY
id_product
)
AS t1
ON
ps_2product_lang.id_product = t1.id_product
And now the more JOINs there are in query the less results i get from DB as if JOINs worked like ANDs.
As i mentioned already some attributes are not assigned to products therefore no quantities. In those id like to fillem with NULL value but i havent included that in query (i thought that JOIN will return nulls to query results)
What am I doing wrong?
Thanks a lot in advance
