Simple non-equi Join query

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

Hello all

I have 3 tables;

tblstaff (staffID (pk), FName, SName) ' Stores Staff names
tblTeam (TeamID (pk), TeamName) ' Stores Team names
tblTeamStaff (TeamStaffID(pk) TeamID (fk), StaffID (fk)) ' stores which
staff are in which teams

I need a query to tell me which staff are not in teams

Something along the lines of

SELECT tblStaff.StaffID, tblStaff.Fname, tblStaff.SName
FROM tblStaff LEFT JOIN tblTeamStaff ON tblStaff.StaffID =
tblTeamStaff.StaffID
WHERE (((tblStaff.StaffID)<>[tblTeamStaff].[StaffID]));
 
Chris said:
Hello all

I have 3 tables;

tblstaff (staffID (pk), FName, SName) ' Stores Staff names
tblTeam (TeamID (pk), TeamName) ' Stores Team names
tblTeamStaff (TeamStaffID(pk) TeamID (fk), StaffID (fk)) ' stores which
staff are in which teams

I need a query to tell me which staff are not in teams

Something along the lines of

SELECT tblStaff.StaffID, tblStaff.Fname, tblStaff.SName
FROM tblStaff LEFT JOIN tblTeamStaff ON tblStaff.StaffID =
tblTeamStaff.StaffID
WHERE (((tblStaff.StaffID)<>[tblTeamStaff].[StaffID]));
Click to expand...

You're close...

SELECT DISTINCTROW tblStaff.StaffID, tblStaff.Fname, tblStaff.SName
FROM tblStaff LEFT JOIN tblTeamStaff ON ON tblStaff.StaffID =
tblTeamStaff.StaffID
WHERE (((tblTeamStaff.StaffID) Is Null));

hth,

LeAnne
 
ty

LeAnne said:
Chris said:
Hello all

I have 3 tables;

tblstaff (staffID (pk), FName, SName) ' Stores Staff names
tblTeam (TeamID (pk), TeamName) ' Stores Team names
tblTeamStaff (TeamStaffID(pk) TeamID (fk), StaffID (fk)) ' stores which
staff are in which teams

I need a query to tell me which staff are not in teams

Something along the lines of

SELECT tblStaff.StaffID, tblStaff.Fname, tblStaff.SName
FROM tblStaff LEFT JOIN tblTeamStaff ON tblStaff.StaffID =
tblTeamStaff.StaffID
WHERE (((tblStaff.StaffID)<>[tblTeamStaff].[StaffID]));
Click to expand...

You're close...

SELECT DISTINCTROW tblStaff.StaffID, tblStaff.Fname, tblStaff.SName
FROM tblStaff LEFT JOIN tblTeamStaff ON ON tblStaff.StaffID =
tblTeamStaff.StaffID
WHERE (((tblTeamStaff.StaffID) Is Null));

hth,

LeAnne
Click to expand...
 
yw!
ty

Chris wrote:

Hello all

I have 3 tables;

tblstaff (staffID (pk), FName, SName) ' Stores Staff names
tblTeam (TeamID (pk), TeamName) ' Stores Team names
tblTeamStaff (TeamStaffID(pk) TeamID (fk), StaffID (fk)) ' stores which
staff are in which teams

I need a query to tell me which staff are not in teams

Something along the lines of

SELECT tblStaff.StaffID, tblStaff.Fname, tblStaff.SName
FROM tblStaff LEFT JOIN tblTeamStaff ON tblStaff.StaffID =
tblTeamStaff.StaffID
WHERE (((tblStaff.StaffID)<>[tblTeamStaff].[StaffID]));
Click to expand...

You're close...

SELECT DISTINCTROW tblStaff.StaffID, tblStaff.Fname, tblStaff.SName
FROM tblStaff LEFT JOIN tblTeamStaff ON ON tblStaff.StaffID =
tblTeamStaff.StaffID
WHERE (((tblTeamStaff.StaffID) Is Null));

hth,

LeAnne
Click to expand...
Click to expand...
 
Back
Top