Help with SQL syntax Left Join + Count

[jasystweb]

I'm not quite sure how to do this join:

Table1: Products
--------------------------
Prod_ID | Prod_Name | etc

Table2: Images
---------------------------
IMG_ID | Prod Name | Prod_ID |

Select Prod_ID, Count(i_Prod_ID) from tblImages right join tblProducts
on tblProducts.Prod_ID = tblImage.i_Prod_ID

Does not produce what I need.....

The desired end result is to produce a row for each product ID that contains the product ID and the number of images that product ID has

Desired Result:
------------------------------
Prod_ID | # of pic ids assigned to this product
1 | 5
2 | 6
3 | 0

[apegram]

I believe you're going to have to get a count from the images table before you join to the products table, and then you can use the ifnull function to make the count 0 where it may be null.

select p.prod_id, ifnull(x.img_count, 0) as img_count
from products p
left join
(select prod_id, count(*) as img_count
from images
group by prod_id) x
on p.prod_id = x.prod_id

Works for me, at least. I created a couple of temp tables to match your schema and loaded 3 products, the first with 6 images, the second with 3, the third with none. The results of the query:

prod_id	img_count
1	6
2	3
3	0

[jasystweb]

That is exactly what I needed. I'll be studying this for a bit to make sure I understand how this is being done. Thank you apegram I was unaware of the ifnull function.