mysql - SQL query not giving exact result -


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:

enter image description here

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