By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,290 Members | 1,395 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,290 IT Pros & Developers. It's quick & easy.

count field in database only returning values > 1

P: 36
Hello
I have the following query

Expand|Select|Wrap|Line Numbers
  1. SELECT COUNT(ur.resourceId) AS times_downloaded
  2. FROM Resource as r, user_resource as ur
  3. WHERE r.resourceId = ur.resourceId
  4. Group BY ur.resourceId
  5.  
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 ?
Expand|Select|Wrap|Line Numbers
  1. WHERE r.resourceId = ur.resourceId
  2.  
Does anyone know how to fix this?
If the resource has not yet been downloaded it should still appear with 0 times downloaded
Jul 17 '07 #1
Share this Question
Share on Google+
4 Replies


mwasif
Expert 100+
P: 801
Use LEFT JOIN

Expand|Select|Wrap|Line Numbers
  1. SELECT COUNT(ur.resourceId) AS times_downloaded
  2. FROM Resource AS r 
  3. LEFT JOIN user_resource AS ur 
  4. ON r.resourceId = ur.resourceId
  5. Group BY ur.resourceId
Jul 17 '07 #2

P: 36
Use LEFT JOIN

Expand|Select|Wrap|Line Numbers
  1. SELECT COUNT(ur.resourceId) AS times_downloaded
  2. FROM Resource AS r 
  3. LEFT JOIN user_resource AS ur 
  4. ON r.resourceId = ur.resourceId
  5. Group BY ur.resourceId
could you please explain what this does in this case?
as opposed to right join and inner join?

Thanks
Jul 17 '07 #3

P: 36
Use LEFT JOIN

Expand|Select|Wrap|Line Numbers
  1. SELECT COUNT(ur.resourceId) AS times_downloaded
  2. FROM Resource AS r 
  3. LEFT JOIN user_resource AS ur 
  4. ON r.resourceId = ur.resourceId
  5. Group BY ur.resourceId
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
Jul 18 '07 #4

P: 36
Use LEFT JOIN

Expand|Select|Wrap|Line Numbers
  1. SELECT COUNT(ur.resourceId) AS times_downloaded
  2. FROM Resource AS r 
  3. LEFT JOIN user_resource AS ur 
  4. ON r.resourceId = ur.resourceId
  5. Group BY ur.resourceId

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

thanks alot
Jul 18 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.