You are here: Forums Ask a Rails expert sql count if user_id exists in...
Posted in Forums : Ask a Rails expert
Authority 25
Posting Rating 0
Sign in to rate this post
|
Hi, i want a query where there were two tables called nutrition_trackers fitness_trackers i am using the sql like this but getting count of users if exists in any 1 table though SELECT count(distinct fitness_trackers.user_id) AS count_all, DATE_FORMAT(workout_day, ‘%b %Y’) AS date_format FROM fitness_trackers join nutrition_trackers ON fitness_trackers.user_id = nutrition_trackers.user_id WHERE (fitness_trackers.workout_day BETWEEN ‘2008-01-01’ and ‘2008-07-31’ and nutrition_trackers.updated_at BETWEEN ‘2008-01-03’ and ‘2008-07-31’) GROUP BY DATE_FORMAT(workout_day, ‘%b %Y’) o/p => count_all date_format 2 Feb 2008 2 Mar 2008 1 Apr 2008 But i want to get output like this as there was no user with id 2 in fitness_trackers in March 2008 where id 1 was present in both tables in March should get count 1 here, any help ?? thanks |
Authority 12
Posting Rating 97
Sign in to rate this post
|
I think your join condition is lacking the year and month. |
Authority 25
Posting Rating 0
Sign in to rate this post
|
Hi, i hope i was using year and month in BETWEEN conditions |
Authority 12
Posting Rating 97
Sign in to rate this post
|
Try adding the year and month on your join condition: ON fitness_trackers.user_id = nutrition_trackers.user_id AND DATE_FORMAT(updated_at, ‘%b %Y’) = DATE_FORMAT(workout_day, ‘%b %Y’) WHERE … That might do the trick, but I’m pretty sure it won’t scale well… |
Authority 25
Posting Rating 0
Sign in to rate this post
|
Hi Erol, |
Ask a Rails expert : Use Rails to develop sites for both Designer and Programmer
Ask a Rails expert : Rails+RS232
Ask a Rails expert : Is this a good way to add Admin section
Ask a Rails expert : RSS feed maker in rails 2.1
Ask a Rails expert : Syncing with ugly legacy databases
Ask a Rails expert : juggernaut Error
Ask a Rails expert : gem "chronic" error
Ask a Rails expert : gem install error
Ask a Rails expert : need your help or views for distributed programming with ruby
Ask a Rails expert : how to refresh ruby files without restart production server