Left join, if table empty only one result [BEGINNER] EMERGENCY

[webbhelp]

Hi!

 

I am getting crazy about this problem. I really need your help because the deadline is today and this is the last thing to do.

 

I got a lot of tables here:

 

products (all products)

products_lang (All translation to the product, title and description)

products_img (All the images to each product)

customer(All customer, name, email etc.)

customer_products(All products the customer bought)

customer_img (Every product each customer buy; there is a possibility to upload an image to that product, an attached image) //THIS ONE IS A BITCH!

 

Okay! I wan't to select all the products (`products`) with the ID's from the table `customer_products` WHERE the customer_products.customer = 3.

Then I select the products the customer has bought. and with that I want the products images, AND the images the customer uploaded to each image.

The problem here is that I only receive one row when I should get 4 rows.AND I don't get the C_imgurl (customer_img.imgurl).

 

BUT if I remove:

LEFT JOIN `customer_img` ON `customer_img`.`product` = `customer_products`.`product`

Then it works; I get the 4 rows.

Good to know maybe, is that the customer_img is empty, that is why I use left JOIN

SELECT 
`products`.`id`,
`products`.`outprice`AS price,
`products`.`vat`,
`products`.`imgupload`, 
`products_lang`.`title`,
`products_img`.`imgurl`,
count(customer_img.id) as countimg,
`customer_img`.`imgurl` AS c_imgurl,
`categories`.`category` 
FROM (`products`)
JOIN `customer_products`ON `customer_products`.`product` = `products`.`id` 
LEFT JOIN `customer_img` ON `customer_img`.`product` = `customer_products`.`product`
LEFT JOIN `products_lang` ON `products_lang`.`product` = `products`.`id` 
LEFT JOIN `products_img` ON `products_img`.`id` = `products`.`firstimg` 
JOIN `categories` ON `categories`.`id` = `products`.`category` 
WHERE `customer_products`.`customer` = 3 AND `products_lang`.`language` = '3'

Please help me with this!

 

Thanks!

Ask if you don't understand!

[Barand]

I see you have a WHERE condition on a LEFT JOINed table. It needs to be in the JOIN condition

 

Try

SELECT 
`products`.`id`,
`products`.`outprice`AS price,
`products`.`vat`,
`products`.`imgupload`, 
`products_lang`.`title`,
`products_img`.`imgurl`,
count(customer_img.id) as countimg,
`customer_img`.`imgurl` AS c_imgurl,
`categories`.`category` 
FROM (`products`)
JOIN `customer_products`ON `customer_products`.`product` = `products`.`id` 
LEFT JOIN `customer_img` ON `customer_img`.`product` = `customer_products`.`product`
LEFT JOIN `products_lang` ON `products_lang`.`product` = `products`.`id` AND `products_lang`.`language` = '3'
LEFT JOIN `products_img` ON `products_img`.`id` = `products`.`firstimg` 
JOIN `categories` ON `categories`.`id` = `products`.`category` 
WHERE `customer_products`.`customer` = 3 

[webbhelp]

Now my result look like this:

 

 

 

1157 1000 12 1 llllllllllllllllll sverige2.png 0 NULL Apple

That is one row, null is the imgurl. and it will be null because there is no images. so it is correct.

BUT the would be 3 row more.

Because in customer_products there is: 4 rows, with same customer(id), and different product(id).

 

so I don't understand why the 3 other result doesn't show?

[Barand]

Do the other 3 have matching records in the product table and those products have matching category records?

[webbhelp]

I attached a file with the tables.

I really don't understand why it don't work now, I think it's all connected with right column, table, but I understand it's not, can you see anything?

Thank you!

Edited November 25, 2013 by webbhelp

[webbhelp]

SELECT 
`products`.`id`,
`products`.`outprice`AS price,
`products`.`vat`,
`products`.`imgupload`, 
`products_lang`.`title`,
`products_img`.`imgurl`,
count(customer_img.id) as countimg,
`customer_img`.`imgurl` AS c_imgurl,
`categories`.`category` 
FROM (`products`)
JOIN `customer_products`ON `customer_products`.`product` = `products`.`id` 
LEFT JOIN `customer_img` ON `customer_img`.`product` = `customer_products`.`product`
LEFT JOIN `products_lang` ON `products_lang`.`product` = `products`.`id` AND `products_lang`.`language` = '3'
LEFT JOIN `products_img` ON `products_img`.`id` = `products`.`firstimg` 
JOIN `categories` ON `categories`.`id` = `products`.`category` 
WHERE `customer_products`.`customer` = 3 

JOIN `customer_products`ON `customer_products`.`product` = `products`.`id`

//using this to only fetch the products where customers_productds -> productid is the same as the product.id; this works I get the product data

 

 LEFT JOIN `customer_img` ON `customer_img`.`product` = `customer_products`.`product`

//Getting all the images connected to the customer_products. IF I remove this line, all the 4 products is received

//If this line is in use I get:

 

1157 1000 12 1 llllllllllllllllll sverige2.png 0 NULL

Apple

 

But I am suppose to get 3 more rows.

---

 

LEFT JOIN `products_lang` ON `products_lang`.`product` = `products`.`id` AND `products_lang`.`language` = '3'

//Selecting all translations to the product., this works I get the title.

 

LEFT JOIN `products_img` ON `products_img`.`id` = `products`.`firstimg`

//Selecting the image to the product, this works, I get the image

[Barand]

Try removing

count(customer_img.id) as countimg,

You have an aggregation without GROUP BY.

But, if you add a group by then you will get only a single row per product even if there are multiple images

 

edit :

or add GROUP BY products.id

Edited November 25, 2013 by Barand

[webbhelp]

IT WORKED! YOUR AWESOME! GREAT! everything good in this world!

 

But what should I do to get the count?

 

wow! wow! how did you found it? that was farfetched. Impressive! =)

[Barand]

You can put the count back if you add the GROUP BY products.id (see my edit to prev post)

[webbhelp]

I used GROUP BY now! and it works!

You really saved my day! I appreciate this more than you can imagine.

I hope you understand that!

 

Thanks a lot for helping me with this! Wish I was good as you!

[Barand]

I had been concentrating on the joins but then had a look at what you were selecting and spotted the COUNT(). If you have an aggregation without a group by then you get a single aggregation for the whole selection. Just sorry I didn't spot it earlier for you to reduce your deadline stress level