Member Avatar for ninjaimp

Hi

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

select 
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

  • 2 Contributors
  • 1 Reply
  • 83 Views
  • 17 Hours Discussion Span
  • Latest Post Latest Post by sandeepparekh9
Member Avatar for sandeepparekh9

can u post the column structure of your two tables.. i mean wat columns your two tables contain..

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.