I need to show first and last name of entities in the staff table that appear in the StaffSoldVehicl-CodePudding

This is all I had, I don't know if its even on the right track, this just shows all entities that appear in both tables staffNumber is the primary key of the staff table and is an attribute in the StaffSoldVehicle table

SELECT DISTINCT Staff.firstName, Staff.lastName FROM Staff, StaffSoldVehicle WHERE Staff.staffNumber=StaffSoldVehicle.staffNumber

I then found this on here that got closer

SELECT firstname, lastName
FROM Staff AS name
INNER JOIN 
  (
  SELECT distinct staffNumber 
  FROM StaffSoldVehicle
  GROUP BY staffNumber
  HAVING COUNT(staffNumber) > 1
  ) as cnt
 ON name.staffNumber = cnt.staffNumber;

I just don't know how to add the fact that the Staff entity's gender attribute must be 'm'

any help would be awesome

CodePudding user response：

Don't need a nested query, just join tables and GROUP BY Staff attributes. Consider:

SELECT Staff.staffNumber, Staff.firstName, Staff.lastName, Count(*) AS CountOfStaff
FROM Staff 
INNER JOIN StaffSoldVehicle ON Staff.staffNumber = StaffSoldVehicle.staffNumber
WHERE (((Staff.gender)="m"))
GROUP BY Staff.staffNumber, Staff.firstName, Staff.lastName
HAVING (((Count(*))>10));