sql query for export all images url

[Asep Komarudin]

Hi,, anyone can help me with sql query?
I have a query for export products with combination,, but I only get 1 images for 1 combination item.
example if I have 1 products with 2 combination and 3 images in each combination,, this query only give me 2 images url.
how can I get all images url?
 
*sorry for my english, hope someone can help me,, thanks..
 

SELECT
p.active 'Active',
m.name 'Manufacturer',
p.id_product 'Product number',
p.reference 'Reference',
pl.name 'Product name',
GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") AS 'Combination',
s.quantity 'Quantity',
p.price 'Price w/o VAT',
pa.price 'Combination price',
p.wholesale_price 'Wholesale price',
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") AS 'Product groups',
p.weight 'Weight',
p.id_tax_rules_group 'TAX group',
pa.reference 'Combination reference',
pl.description_short 'Short description',
pl.description 'Long description',
pl.meta_title 'Meta Title',
pl.meta_keywords 'Meta Keywords',
pl.meta_description 'Meta Description',
pl.link_rewrite 'Link',
pl.available_now 'In stock text',
pl.available_later 'Coming text',
p.available_for_order 'Orderable text',
p.date_add 'Added',
p.show_price 'Show price',
p.online_only 'Only online',
CONCAT("http://", su.domain, su.physical_uri , IF(pai.id_image IS NOT NULL,pai.id_image,im.id_image), "/", REPLACE(REPLACE(REPLACE(pl.name," ","_"),"\"",""),"#",""), ".jpg") as "Image URL"

FROM
ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product and pl.id_lang=2)
LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category and cl.id_lang=2)
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product and (pa.id_product_attribute=s.id_product_attribute or pa.id_product_attribute is null))
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute and al.id_lang=2)
LEFT JOIN ps_shop sh ON p.id_shop_default = sh.id_shop 
LEFT JOIN ps_shop_url su ON su.id_shop = sh.id_shop AND su.main = 1
LEFT JOIN ps_image im ON (p.id_product = im.id_product AND im.cover = 1)
LEFT JOIN ps_product_attribute_image pai ON (pai.id_product_attribute = s.id_product_attribute)
GROUP BY p.id_product,pac.id_product_attribute order by p.id_product

[Asep Komarudin]

No answer for this question

well,, I'm done with this problem..

Maybe someone need it, I'll share a query.

 

Thanks.

SELECT
p.active 'Active',
m.name 'Manufacturer',
p.id_product 'Product number',
p.reference 'Reference',
pl.name 'Product name',
GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") AS 'Combination',
s.quantity 'Quantity',
p.price 'Price w/o VAT',
pa.price 'Combination price',
p.wholesale_price 'Wholesale price',
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") AS 'Product groups',
p.weight 'Weight',
p.id_tax_rules_group 'TAX group',
pa.reference 'Combination reference',
pl.description_short 'Short description',
pl.description 'Long description',
pl.meta_title 'Meta Title',
pl.meta_keywords 'Meta Keywords',
pl.meta_description 'Meta Description',
pl.link_rewrite 'Link',
pl.available_now 'In stock text',
pl.available_later 'Coming text',
p.available_for_order 'Orderable text',
p.date_add 'Added',
p.show_price 'Show price',
p.online_only 'Only online',
GROUP_CONCAT("http://", su.domain, su.physical_uri , IF(pai.id_image IS NOT NULL,pai.id_image,im.id_image), "/", REPLACE(REPLACE(REPLACE(pl.name," ","_"),"\"",""),"#",""), ".jpg") as "Image URL"

FROM
ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product and pl.id_lang=2)
LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category and cl.id_lang=2)
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product and (pa.id_product_attribute=s.id_product_attribute or pa.id_product_attribute is null))
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute and al.id_lang=2)
LEFT JOIN ps_shop sh ON p.id_shop_default = sh.id_shop 
LEFT JOIN ps_shop_url su ON su.id_shop = sh.id_shop AND su.main = 1
LEFT JOIN ps_image im ON (p.id_product = im.id_product AND im.cover = 1)
LEFT JOIN ps_product_attribute_image pai ON (pai.id_product_attribute = s.id_product_attribute)
GROUP BY p.id_product,pac.id_product_attribute order by p.id_product

[[email protected]]

Got error,

 

2 errors 

1. The sql field is too long (1000 chars max).
2. Error

[Ballantrae Shop]

Hello, sorry for the trouble, the code that inserted in the second post is the correct one, right. This code entered directly into SQL Manager and then into SQL Query? Thank you very much for your help, unfortunately I'm not very experienced

[monicaamb]

Hello, thank you for your help.

I got this error:

FROM doesn't exit