mysql query problem

[emvoo]

Hi there

 

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 fill`em with NULL value but i havent included that in query (i thought that JOIN will return null`s to query results)

 

What am I doing wrong?

 

Thanks a lot in advance

[electriz]

You are getting less results, because that's how the JOIN works. There must be a match in both tables.

 

Read about LEFT JOIN, RIGHT JOIN, INNER JOIN, OUTER JOIN.

[emvoo]

yeah ive figured that out. i used Left join. also i didnt need to use order by in repeated part of query.

thanks