Browse the Ruby on Rails Community.

You are here: Forums Ask a Rails expert sql count if user_id exists in...

Replytotopic

sql count if user_id exists in 2 tables

Posted in Forums : Ask a Rails expert

 
Profile

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
id user_id updated_at id user_id workout_day
1 2 2008-02-20 1 2 2008-02-23
2 2 2008-03-23 2 1 2008-02-23
3 1 2008-02-20 3 1 2008-03-20
4 1 2008-03-10 4 1 2008-04-30
5 1 2008-04-15
and now i want the count of users that were present in both tables.

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
o/p => count_all date_format 2 Feb 2008 1 Mar 2008 1 Apr 2008

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,
i want to get count like this but my sql was retrieving those who were present in atleat 1 table too, can we refine my sql to get the perfect count for particular months

any help ??

thanks

 
N739949849_5167

Authority 12
Posting Rating 97
Sign in to rate this post

I think your join condition is lacking the year and month.

 
Profile

Authority 25
Posting Rating 0
Sign in to rate this post

Hi,
Here is the join using
‘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’)’

i hope i was using year and month in BETWEEN conditions
thanks,

 
N739949849_5167

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…

 
Profile

Authority 25
Posting Rating 0
Sign in to rate this post

Hi Erol,
Thanks for the reply and this works good in my app.

Replytotopic

Other Recent Topics

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

Formatting Help
  • *bold*       _italics_      
    bq. (quotes)
  • "DSC":http://www.dsc.net
  • * or # (lists)
or cancel