Prio: What GROUP BY to use?

[webbhelp]

Hi!

 

I got this small irritating problem here, how should I use group by?

I am joining a lot of tables here and this SQL is used in a webshop.

 

Okey, the question:
I want to get all images from customer_img connected to each product.

in this example, I got one product with a image in customer_img connected to it AND I got one product with 2 images in customer_img connected to it.

 

But when I run this query, I only get one image each to those products, when I am supposed to get 1 image to the first product and 2 images to the other product.

 

I think the problem is that I use wrong column in group BY, but I don't know.

SEE IMAGE

SELECT products.id AS id,
                                products.vat,
                                products.outprice AS price,
                                products.weight,
                                products.imgupload,
                                products_img.imgurl AS pimgurl,
                                `customer_products`.`number`,
                                `products_lang`.`title`,
                                `customer_img`.`imgurl`,
                                `customer_products`.`number`,
                                
                                count(customer_img.id) AS countimg,
                                
                                (`products`.`outprice` * `customer_products`.`number`) + (count(customer_img.id) * 150) AS total,
                                round( ((`products`.`outprice` * `customer_products`.`number`) + (count(customer_img.id) * 150)) * (1 - products.vat / 100)) AS brutto,
                                round( ((`products`.`outprice` * `customer_products`.`number`) + (count(customer_img.id) * 150)) * (products.vat / 100)) AS countvat
                                
                            FROM (`customer_products`) 
                            JOIN `products` ON `products`.`id` = `customer_products`.`product` 
                            LEFT JOIN `products_lang` ON 
                            	`products_lang`.`product` = `customer_products`.`product` AND
                                    products_lang.language = ?
                            LEFT JOIN products_img ON products_img.id = products.firstimg
                            LEFT JOIN `customer_img` ON 
                            	`customer_img`.`product` = `customer_products`.`product` 
                            
                            WHERE `customer_products`.`customer` = ?
                            GROUP BY customer_products.product

[gizmola]

When you join table a to table b, you get a row every time the join criteria can be matched.

 

When you group by you suppress all the joins, in favor of a single row per grouping.

 

If you have n matching images per product, then you will get n rows, each with a different image.

 

Your group by is suppressing these rows.

[webbhelp]

Thanks for your reply!

 

Okey so the group by suppress the result of rows!

But when I Don't use group by, I only get one row :S?

 

I don't see a solution here?

 

Thanks in advance