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
