Duplicated list of product list, and how to fix it.

[Wilfredcy]

As you can see the file below, the product info is duplicated a few times.

 Here is my SQL query.

SELECT 

a.`id_product`,
b.`name`,
a.`reference`AS `main_sku`,
pa.`reference` AS `sku`,
b.`link_rewrite` AS `url`,
a.`price` AS `price`,
sav.`quantity`,
m.`name` AS `Manufacturer`

FROM `ps_product` a 
LEFT JOIN `ps_product_lang` b ON (b.`id_product` = a.`id_product`) 
LEFT JOIN `ps_stock_available` sav ON (sav.`id_product` = a.`id_product`) 
LEFT JOIN `ps_manufacturer` m ON (a.id_manufacturer = m.id_manufacturer)
LEFT JOIN  `ps_product_attribute` pa ON (pa.`id_product` = a.`id_product`)

WHERE 1 ORDER BY a.`id_product` ASC        

 

[Rodrigo B Laurindo]

FROM `ps_product` a 
LEFT JOIN `ps_product_lang` b ON (b.`id_product` = a.`id_product`) 
LEFT JOIN `ps_manufacturer` m ON (a.id_manufacturer = m.id_manufacturer)
LEFT JOIN  `ps_product_attribute` pa ON (pa.`id_product` = a.`id_product`)
LEFT JOIN `ps_stock_available` sav ON ((sav.`id_product` = a.`id_product`) AND (sav.id_product_attribute is null OR pa.id_product_attribute = sav.id_product_attribute)

Try this way (not tested) 

Edited April 1, 2019 by Rodrigo B Laurindo (see edit history)

[Wilfredcy]

Hi, after test with your code, the duplicated products are reduced, but it still has a double product list there.

[Rodrigo B Laurindo]

Well, it is because of your languages and combinations. How do you want to manege it?

It is shown one product for each combination and language.

[Wilfredcy]

How can I show one language only(English)?

[Rodrigo B Laurindo]

Assuming that the id of english is 1:

WHERE 1 ORDER BY a.`id_product` ASC
AND b.id_lang = 1

 

[Wilfredcy]

Update:

The problem is solved, thank for the help Rodrigo B Laurindo.

Here is the final code

SELECT 

a.`id_product`,
b.`name`,

a.`reference`AS `main_sku`,
pa.`reference` AS `sku`,
b.`link_rewrite` AS `url`,
a.`price` AS `price`,
sav.`quantity`,
m.`name` AS `Manufacturer`

FROM `ps_product` a 
LEFT JOIN `ps_product_lang` b ON ((b.`id_product` = a.`id_product`) AND (b.id_lang = "1"))
LEFT JOIN `ps_manufacturer` m ON (a.id_manufacturer = m.id_manufacturer)
LEFT JOIN  `ps_product_attribute` pa ON (pa.`id_product` = a.`id_product`)
LEFT JOIN `ps_stock_available` sav ON ((sav.`id_product` = a.`id_product`) AND (sav.id_product_attribute is null OR pa.id_product_attribute = sav.id_product_attribute))


WHERE 1 ORDER BY a.`id_product` ASC 

 

[Wilfredcy]

Hi, sorry to bother you again.

 

I just realize that there got another problem, which is the product with no combination cannot show its quantity, how can I fix this?

 

[Rodrigo B Laurindo]

Try this:

SELECT

a.`id_product`,
b.`name`,

a.`reference`AS `main_sku`,
pa.`reference` AS `sku`,
b.`link_rewrite` AS `url`,
a.`price` AS `price`,
sav.`quantity`,
m.`name` AS `Manufacturer`

FROM `ps_product` a 
LEFT JOIN `ps_product_lang` b ON ((b.`id_product` = a.`id_product`) AND (b.id_lang = "1"))
LEFT JOIN `ps_manufacturer` m ON (a.id_manufacturer = m.id_manufacturer)
LEFT JOIN  `ps_product_attribute` pa ON (pa.`id_product` = a.`id_product`)
LEFT JOIN `ps_stock_available` sav ON ((sav.`id_product` = a.`id_product`) AND (coalesce(sav.id_product_attribute,0) = 0 OR pa.id_product_attribute = sav.id_product_attribute))


WHERE 1 ORDER BY a.`id_product` ASC 

 

[Wilfredcy]

Hi, it show the quantity for the product with no combination, but now the product with the combination is duplicated again.

[Rodrigo B Laurindo]

Of course it is duplicating 🤔

SELECT

a.`id_product`,
b.`name`,

a.`reference`AS `main_sku`,
pa.`reference` AS `sku`,
b.`link_rewrite` AS `url`,
a.`price` AS `price`,
sav.`quantity`,
m.`name` AS `Manufacturer`

FROM `ps_product` a 
LEFT JOIN `ps_product_lang` b ON ((b.`id_product` = a.`id_product`) AND (b.id_lang = "1"))
LEFT JOIN `ps_manufacturer` m ON (a.id_manufacturer = m.id_manufacturer)
LEFT JOIN `ps_product_attribute` pa ON (pa.`id_product` = a.`id_product`)
LEFT JOIN `ps_stock_available` sav ON ((sav.`id_product` = a.`id_product`) AND ((coalesce(sav.id_product_attribute,0) = 0 and (pa.id_product_attribute is null))
OR (pa.id_product_attribute = sav.id_product_attribute)))


WHERE 1 ORDER BY a.`id_product` ASC

 

[Wilfredcy]

Hi, I using the code above now it exports empty.

[Rodrigo B Laurindo]

sorry, I can not test It just now, but try to remove one ")" before WHERE

[Wilfredcy]

Hi, it work, THANK YOU

The code:

SELECT 

a.`id_product`,
b.`name`,

a.`reference`AS `main_sku`,
pa.`reference` AS `sku`,
b.`link_rewrite` AS `url`,
a.`price` AS `price`,
sav.`quantity`,
m.`name` AS `Manufacturer`

FROM `ps_product` a 
LEFT JOIN `ps_product_lang` b ON ((b.`id_product` = a.`id_product`) AND (b.id_lang = "1"))
LEFT JOIN `ps_manufacturer` m ON (a.id_manufacturer = m.id_manufacturer)
LEFT JOIN  `ps_product_attribute` pa ON (pa.`id_product` = a.`id_product`)
LEFT JOIN `ps_stock_available` sav ON ((sav.`id_product` = a.`id_product`) AND (coalesce(sav.id_product_attribute,0) = 0 OR (pa.id_product_attribute is null)) OR (pa.id_product_attribute = sav.id_product_attribute))

WHERE 1 ORDER BY a.`id_product` ASC