Simple non-equi Join query

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]));
 
L

LeAnne

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]));

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
 
C

Chris

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]));

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
 
L

LeAnne

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]));

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top