I dont have a huge wealth of experince with SQL statements and im struggling;

i have two tables

table one holds employee data
table two holds payent histroy for employee

so essentially a 1 to many relationship

I would like to list all of the job titles(for which there could be multiples as different employees can have the same job title) in the employee table and find the maximum amount of hours for that job title as the hours could be different for different empoyees.

i have this statement

master.Jobtitle,  count(jobtitle)
from master
LEFT JOIN (select max(payhistory.contractualhours) from payhistory group by contractualhours)
on master.staffno = payhistory.staffno

but i keep gettting an sql error for 'Incorrect syntax near the keyword 'on'.'

I wondered if anyone could help

many thanks