I have to list first and last name of staff and I think I need to join three different tables-CodePudding

List the first and last name of all female staff that have made an appointment for a customer from Canberra for mechanical repair work

I think these are the three tables I need to join

Customer(customerNumber(PK), firstName, lastName, streetNumber, streetName, suburb, postcode, city, state, gender, workRequired, customerRego(FK))

RepairAppointment(repairAppNumber(PK), customernumber(FK), branchNumber(FK), time/date, staffNumber(FK))

Staff(staffNumber(PK), firstName, lastName, position, gender, DOB, salary, sectionNumber(FK), intPhone, branchNumber(FK))

SELECT Staff.firstName, Staff.lastName, Count(*) AS CountOfStaff FROM Staff INNER JOIN RepairAppointment ON Staff.staffNumber = RepairAppointment.staffNumber WHERE (((Staff.gender)="f")) GROUP BY Staff.staffNumber, Staff.firstName, Staff.lastName

This is what I have so far but it doesn't filter if the customer was from city='Canberra'

I'm so lost, help!

CodePudding user response：

Please check if this help? You can change string literals as per the data.

SELECT s.firstName, s.lastName, count(*)
FROM Staff s, RepairAppointment ra, Customer c
WHERE s.staffNumber = ra.staffNumber
AND ra.customerNumber = c.customerNumber
AND c.workRequired = 'Mechanical Repair'
AND c.city = 'Canberra'
AND s.gender = 'f'
and ra.date >= Date()
GROUP BY s.firstName, s.lastName