[SOLVED]SQL to generate list with wholesle price

[ptityop]

Hello,

I would need to generate a list with the following columns

ID, Productname, Product category, Product Ref, Buying price (the price we buy the product for before selling it) , Price EXC Tax and Stock.

I have SQL knowledge so if anyone would be kind enough to help, that will be appreciated. Thanks in advance

 

I have tried the following... but as expected i had no luck...

SELECT 
	p.id_product 'Product ID', 
	p.reference 'Reference', 
	pl.name 'Product Name', 
	p.price 'Price', 
	p.wholesale_price 'Wholesale Price', 
	s.quantity 'Quantity', 
	p.available_for_order 'Available For Order', 
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category and cl.id_lang=2)
LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product)
GROUP BY p.id_product,
order by p.reference

 

Edited April 12, 2023 by ptityop
Solved (see edit history)

[Slava]

Try this:

SELECT 
	p.id_product 'Product ID',
	p.reference 'Reference',
	pl.name 'Product Name',
	p.price 'Price',
	p.wholesale_price 'Wholesale Price',
	s.quantity 'Quantity',
	p.available_for_order 'Available For Order'
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category and cl.id_lang=2)
LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product)
GROUP BY p.id_product
ORDER by p.reference

[ptityop]

That worked thank you .... comma issue then .. I could have spent hours on this !

[Slava]

Exactly... comma's 😉