sql statement

[Nathan Henderson]

Hi all,
I have two table both with the same Unique Identifier field (staffID) I need
to construct a sql statement that lists the records in the first table as
long as their staffID does not exist in the second table.
Example table:

Table 1.
StaffID, Firstname, Surname

Table 2
StaffID, LoggedIn, Dte


Can anyone help me out with this?

cheers,

nathan.

 

[Tom Ellison]

Dear Nathan:

Two ways. I prefer:

SELECT T1.*
FROM [Table 1] T1
LEFT JOIN [Table 2] T2
ON T2.StaffID = T1.StaffID
WHERE T2.StaffID IS NULL

Another is:

SELECT *
FROM [Table 1] T1
WHERE NOT EXISTS (SELECT *
FROM [Table 2] T2
WHERE T2.StaffID = T1.StaffID)

Tom Ellison

 

[Gary Townsend \(Spatial Mapping Ltd.\)]

This should do the trick for you:

SELECT Table1.staffid, Table1.firstname, Table1.surname
FROM Table1 LEFT JOIN Table2 ON Table1.staffid = Table2.StaffID
WHERE (((Table2.StaffID) Is Null));

You will need to chagne values to suit your needs but basically just left
join the staff table to the log table.

Then jsut find all the instances where the staff ID on the log table is
null.

You may have to do some grouping as well but in essence this is what you
need.

Gary Townsend
Spatial Mapping Ltd.

 

[Gary Townsend \(Spatial Mapping Ltd.\)]

Tom types faster than i do

This should do the trick for you:

SELECT Table1.staffid, Table1.firstname, Table1.surname
FROM Table1 LEFT JOIN Table2 ON Table1.staffid = Table2.StaffID
WHERE (((Table2.StaffID) Is Null));

You will need to chagne values to suit your needs but basically just left
join the staff table to the log table.

Then jsut find all the instances where the staff ID on the log table is
null.

You may have to do some grouping as well but in essence this is what you
need.

Gary Townsend
Spatial Mapping Ltd.

 

[Tom Ellison]

Dear Gary:

No hard feelings!

Tom Ellison

Tom types faster than i do