i want write sql query should return total number of tutors teaching specific subject in specific town.
this query
select count(*) `tutor_main` tm join `tutor_subjects` ts on tm.`tutor_id` = ts.`tutor_id` join `sub_subjects` ss on ss.`subs_id` = ts.`subs_id` join `town` tt on tm.`town_id` = tt.`town_id` join `subject_level` sl on sl.`sub_level_id` = ts.`sub_level_id` ts.`subs_id` = 1 , tm.`town_id` = 1
here tutor_main
main table stores town_id
foreign key. sub_subjects
main subjects table , subject_level table stores subject levels. tutor_subjects
table stores keys of tutor_id, subs_id , sub_level_id.
this screenshot of tutor_subjects
table:
note town_id
tutors 1.
now when run query, gives me 8 in output. correct output should 5 because there 5 tutors teaching subject id 1 , town id 1.
please help.
select count(distinct tm.`tutor_id`) `tutor_main` tm join `tutor_subjects` ts on tm.`tutor_id` = ts.`tutor_id` join `sub_subjects` ss on ss.`subs_id` = ts.`subs_id` join `town` tt on tm.`town_id` = tt.`town_id` join `subject_level` sl on sl.`sub_level_id` = ts.`sub_level_id` ts.`subs_id` = 1 , tm.`town_id` = 1
Comments
Post a Comment