Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

sql statement

Discussion in 'Information Technology' started by Nathan Henderson, Jul 28, 2006.

Tags:
  1. Nathan Henderson

    Nathan Henderson
    Expand Collapse
    Guest

    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.
     
  2. Loading...


  3. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    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


    "Nathan Henderson" <nathan@onlineze.com.au> wrote in message
    news:OX8fA4GeGHA.3364@TK2MSFTNGP05.phx.gbl...
    > 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.
    >
     
  4. 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.

    "Nathan Henderson" <nathan@onlineze.com.au> wrote in message
    news:OX8fA4GeGHA.3364@TK2MSFTNGP05.phx.gbl...
    > 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.
    >
    >
     
  5. Tom types faster than i do

    "Gary Townsend (Spatial Mapping Ltd.)" <garytNADDASPAM@spatialmapping.com>
    wrote in message news:7l7ag.15376$Qq.9443@clgrps12...
    > 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.
    >
    > "Nathan Henderson" <nathan@onlineze.com.au> wrote in message
    > news:OX8fA4GeGHA.3364@TK2MSFTNGP05.phx.gbl...
    > > 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.
    > >
    > >

    >
    >
     
  6. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    Dear Gary:

    No hard feelings!

    Tom Ellison


    "Gary Townsend (Spatial Mapping Ltd.)" <garytNADDASPAM@spatialmapping.com>
    wrote in message news:6m7ag.15379$Qq.13080@clgrps12...
    > Tom types faster than i do
    >
    > "Gary Townsend (Spatial Mapping Ltd.)" <garytNADDASPAM@spatialmapping.com>
    > wrote in message news:7l7ag.15376$Qq.9443@clgrps12...
    >> 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.
    >>
    >> "Nathan Henderson" <nathan@onlineze.com.au> wrote in message
    >> news:OX8fA4GeGHA.3364@TK2MSFTNGP05.phx.gbl...
    >> > 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.
    >> >
    >> >

    >>
    >>

    >
    >
     

Share This Page