count field in database only returning values > 1

[Proogeren]

Hello
I have the following query

SELECT COUNT(ur.resourceId) AS times_downloaded
FROM Resource as r, user_resource as ur
WHERE r.resourceId = ur.resourceId
Group BY ur.resourceId

The User_Resource table indicates how many times a user has downloaded a resource. If the resource has not yet been downloaded they will not appear in my query because of the ?

WHERE r.resourceId = ur.resourceId

Does anyone know how to fix this?
If the resource has not yet been downloaded it should still appear with 0 times downloaded

[mwasif]

Use LEFT JOIN

[CODE=mysql]SELECT COUNT(ur.resour ceId) AS times_downloade d
FROM Resource AS r
LEFT JOIN user_resource AS ur
ON r.resourceId = ur.resourceId
Group BY ur.resourceId[/CODE]

[Proogeren]

Use LEFT JOIN

[CODE=mysql]SELECT COUNT(ur.resour ceId) AS times_downloade d
FROM Resource AS r
LEFT JOIN user_resource AS ur
ON r.resourceId = ur.resourceId
Group BY ur.resourceId[/CODE]

could you please explain what this does in this case?
as opposed to right join and inner join?

Thanks

[Proogeren]

Use LEFT JOIN

[CODE=mysql]SELECT COUNT(ur.resour ceId) AS times_downloade d
FROM Resource AS r
LEFT JOIN user_resource AS ur
ON r.resourceId = ur.resourceId
Group BY ur.resourceId[/CODE]

Hi
this did not work in my case. it I have 6 empty fields and it only returns one of them....

Table User_Resource
UserResourceId int(11)
ResourceId int(11)

Table Resource
ResourceId int(11)


Table Resource contains 15 resources and only nine of them have been downloaded. But I only see 10 of them in my list. Plz help

[Proogeren]

Use LEFT JOIN

[CODE=mysql]SELECT COUNT(ur.resour ceId) AS times_downloade d
FROM Resource AS r
LEFT JOIN user_resource AS ur
ON r.resourceId = ur.resourceId
Group BY ur.resourceId[/CODE]

I got it to work. I had to take group by r.resourceId and not ur.resourceId

thanks alot