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