SQL query, exclude products with 0 quantity?

[4thbasement]

Hi all, 

 

I don't have a lot of experience with SQL. I'm working on a query to give me specific information I need right now, and the query below is essentially it, but it could be better .  I put it together from a dozen different threads/posts around here so thank you presta forum!

SELECT pa.upc, m.name AS  'manufacturer', pa.reference, p.price, pl.name, GROUP_CONCAT( DISTINCT (
pal.name
)
SEPARATOR  ", " ) AS combination, pq.quantity, p.id_product, pl.meta_description
FROM ps_product p
LEFT JOIN ps_manufacturer m ON ( p.id_manufacturer = m.id_manufacturer ) 
LEFT JOIN ps_product_attribute pa ON ( p.id_product = pa.id_product ) 
LEFT JOIN ps_stock_available pq ON ( p.id_product = pq.id_product
AND pa.id_product_attribute = pq.id_product_attribute ) 
LEFT JOIN ps_product_lang pl ON ( p.id_product = pl.id_product ) 
LEFT JOIN ps_product_attribute_combination pac ON ( pa.id_product_attribute = pac.id_product_attribute ) 
LEFT JOIN ps_attribute_lang pal ON ( pac.id_attribute = pal.id_attribute ) 
LEFT JOIN ps_product_attribute_image pai ON ( pa.id_product_attribute = pai.id_product_attribute ) 
WHERE pl.id_lang =1
AND pal.id_lang =1
GROUP BY pac.id_product_attribute
ORDER BY m.name, pa.reference

My questions is, how can I modify the above to exclude a combination that has 0 quantity?

 

Thank you!

 

[endriu107]

You need to add: AND pq.quantity != 0 before GROUP BY.

[4thbasement]

Thank you endriu, it worked! 

 

There is something about this query that's bugging me also.. 

 

It does show the quantity of products that have more than one attribute (color, size), but it seems to randomly pick which to show first,  color or size, for example: 

 

black, 8,

black, 8.5,

7.5, black,

black, 7

 

Is there an easy way to separate the attributes to show in a different column OR make one of them always first?